PostgreSQL General Bits is free to the PostgreSQL community.
PostgreSQL General Bits is published each week in order to promote a better understanding of PostgreSQL. It has been published since November 2002 and the number of issues has reached 76.

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!

Editor: elein at varlena.com

Different Database Locations [GENERAL] Multiple databases on separate drives/file systems? 08-Jun-2004

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 PGDATA2

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.

Contributors: Scot L. Harris webid at cfl.rr.com, Richard Huxton dev at archonet.com, Jim Seymour jseymour at linxnet.com, Tom Lane tgl at sss.pgh.pa.us

Expression columns in Views [GENERAL] virtual fields on VIEW? 18-Jun-2004

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.

Contributors: raptorat tvskat.net Richard Huxton dev at archonet.com,

Readable Queries Little hints for Writing Readable Queries 19-Jun-2004

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.

• Use consistent capitalization to your taste
• Use multiple lines
• Indent levels and continuation lines to your taste.
• Use aliases on all of your tables
  • Make sure you know exactly which table your column is coming from
• Use explicit joins to isolate table relations
  • A better alternative is to name foreign keys the same name as the referencing table so that you can use "USING (key)" instead of ON expr.

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 the original.
BEFORE

	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;

	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.

Contributors: elein at varlena.com

Big Items for Release 7.5 Quick review of Features for 7.5 20-Jun-2004

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.

• Tablespaces
• Nested Transactions
• Two-Phase Commit
• Integrated pg_autovacuum
• PITR
• Native Win32
• Replication (Slony-I) *
• Server-side Java *
• Enhanced server-side Perl *

• Allow logging of only data definition(DDL), or DDL and modification statements
• Allow log lines to include session-level information, like database and user
• Change factorial to return a numeric
• Allow pg_dump to dump sequences using NO_MAXVALUE and NO_MINVALUE
• Prevent whole-row references from leaking memory, e.g. SELECT COUNT(tab.*)
• Make LENGTH() of CHAR() not count trailing spaces
• Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8, float4, numeric/decimal
• Add NO WAIT LOCKs
• COMMENT ON [ CAST | CONVERSION | OPERATOR CLASS | LARGE OBJECT | LANGUAGE ]
• Allow more ISOLATION LEVELS to be accepted
• Have psql \dn show only visible temp schemas using current_schemas()
• Have psql '\i ~/tabtab' actually load files it displays from home dir
• Add GUC setting to make created tables default to WITHOUT OIDS
• ALTER TABLE ADD COLUMN does not honor DEFAULT and non-CHECK CONSTRAINT
• ALTER TABLE ADD COLUMN column DEFAULT should fill existing rows with DEFAULT value
• ALTER TABLE ADD COLUMN column SERIAL doesn't create sequence because of the item above
• Allow ALTER TABLE to modify column lengths and change to binary compatible types
• Allow dump/load of CSV format
• Allow Java server-side programming
• Allow psql \du to show users, and add \dg for groups
• Allow pg_dump to dump CREATE CONVERSION
• Make pg_restore continue after errors, so it acts more like pg_dump scripts
• Print table names with constraint names in error messages, or make constraint names unique within a schema
• Issue NOTICE if foreign key data requires costly test to match primary key
• Use dependency information to dump data in proper order
• Have pg_dump -c clear the database using dependency information
• Change representation of whole-tuple parameters to functions
• Add checks for fclose() failure (Tom)
• Change CVS ID to PostgreSQL
• Exit postmaster if postgresql.conf can not be opened

Contributors: elein at varlena.com