PostgreSQL General Bits is free to the PostgreSQL community.
However, the writing and maintenance do take time and effort. In order to ensure that I can continue to spend time on writing and researching, I encourage you or your employer to subscribe to General Bits. You can use PayPal or you can contact me directly for an alternative.
Help keep PostgreSQL General Bits alive and well!
You can put different databases on different disk drives by having alternative locations for your installation. Traditionally you usually have one PGDATA directory and all of your databases are stored there. However, if you want to distribute your databases across file systems, you can have more than one location for them.
Locations are useful, but will be going out of style for 7.5. In 7.5 tablespaces will replace the functionality of locations by enabling creation of tables, rather than whole databases on specific disks.
There is a little confusion in how the location environment variable is specified if your system does not handle absolute paths. To resolve this problem set an environment variable, PGDATA2, for example, in the start up environment for the postmaster. Often when you use PGDATA, you use it in the shell, expanded, as $PGDATA. In this case, you will be relying on the server to fetch the variable from its local environment.
To create an alternative location, as the postgres superuser, call initlocation
$ initlocation PGDATA2This has to be done as the postgres super user, usually postgres in order for the postmaster process to be able to write in that directory.
Once your location is initialized, people allowed to create databases can do so using the -D or the WITH LOCATION = option to createdb.
$ createdb newdb -D PGDATA or $ createdb newdb WITH LOCATION = PGDATA
The ability to distribute databases across your file system can be helpful to isolate large databases on their own drives.
There is no limitation on the expressions you can put into the target list of a view. You can put on the target list any expression.
If your view is updateable, you will want to modify the INSERT/DELETE/UPDATE triggers on your view in order to handle the generated elements in the way that you see fit.
Here is one example of view definitions using expressions in their target lists.
SELECT ... CASE WHEN date < CURRENT_DATE THEN 'green'::text WHEN date > CURRENT_DATE THEN 'red'::text ELSE 'blue'::text END AS state, (t1.field2 || t2.fieldA) AS stuff FROM ...
Keep in mind when you are producing reports and other displaying output that you may want to have data type formatting, colors, graphics, html formatting or other decorations in the output stream. Views are a good way to ensure that your display is consistent.
The first few recommendations on creating readable queries are both obvious and contentious. Two items that occurred to grzm were using aliases and using explicit joins.
No one is perfectly consistent, but readable queries are
something to aim at. Good habits come from practice.
Here is a brief before and after query grabbed from another
post on pgsql-general. I've used my imagination as
to which columns actually belonged to which table in
select books.id as book_id,title,isbn, publisher, publishers.id as publisher_id, place, places.id as place_id, illustrator, illustrators.id as illustrator_id, edition, editions.id as edition_id, type, types.id as type_id, category, categories.id as category_id, year, binding, binding.id as binding_id, weight, books.price as price,discount,description,comments,books.status, ctime,mtime from books, publishers, places, illustrators, editions, types, categories, binding ,orders_and_books where books.publisher_id=publishers.id and books.place_id=places.id and books.illustrator_id=illustrators.id and books.edition_id=editions.id and books.type_id=types.id and books.category_id=categories.id and books.binding_id=binding.id and orders_and_books.order_id = 753 and orders_and_books.book_id = books.id;AFTER
SELECT b.id AS books_id, b.title, b.isbn, publisher, p.id AS publisher_id, b.place, pl.id AS place_id, b.illustrator, i.id AS illustrator_id, p.edition, e.id AS editions_id, p.type, t.id as type_id, p.category, c.id as category_id, p.binding, b.id as binding_id, p.year, p.description, o.weight, o.price, o.discount, o.comments, o.status, o.ctime, o.time FROM books b JOIN publisher p ON b.publisher_id = p.id JOIN places pl ON b.place_id = pl.id JOIN illustrators i ON b.illustrator_id=illustrators.id JOIN editions e ON p.edition_id=editions.id JOIN types e ON p.type_id=types.id JOIN join categories ON p.category_id=categories.id JOIN join binding ON p.binding_id=binding.id JOIN orders_and_books o ON b.id = o.id WHERE o.order_id = 753;
As you begin to maintain more standard queries and have to dissect what you meant long after you have forgotten the subtleties of the query, then you will be glad to have written clearer queries.
This is a quick list of some of the features that we hope will be available in 7.5. The feature freeze has not yet occurred so nothing is promised until it is checked in and flies. A more formal list will be compiled after the feature freeze.
Be gearing up for beta testing! Do not ignore testing for those features hosted on GBorg. These are marked with * below.
Comments and Corrections are welcome. Suggestions and contributions of items are also welcome. Send them in!
Copyright A. Elein Mustain 2003, 2004, 2005, 2006, 2007, 2008, 2009