Many installation sites have several databases which require utility functions, datatypes and contrib items to be available in all of the databases.

Since databases do not share catalogs, all of these must be defined in each database. But, wait! There is a way to do this efficiently.

Apply your common functions, datatypes and contrib items to template1. Then each database created after this is done will contain your common definitions.

template0 is the base PostgreSQL template database. template1 is created based on template0. All databases in the installation are then created based on template1. This separation allows installation specific definitions to be installed into template1 while maintaining the purity of template0 for emergencies.

Contributors: Matthew Jones jonespm at engin.umd.umich.edu, elein at varlena.com

How Cascades work [SQL] Mechanics of Update:Cascade 31-Jan-2004

CASCADES apply to the tables involved in FOREIGN KEY constrainsts. They determine the action to take when the referenced column is changed or the referenced row is deleted. Although CASCADE is defined on the table defining the FOREIGN KEY, it is triggered by changes to the referenced table.

Suppose we had two simple tables of vendors and a catalog like this with the following information about the vendors and the products in the catalog that they sold. The vendor id field in catalog references the vendor table with the default of NO CASCADES.

	create table vendors (
	   v_id  integer,
	   v_name text,
	   PRIMARY KEY (v_id)
	);
	create table catalog (
	   item_id integer,
	   v_id integer,
	   item_name text,
	   PRIMARY KEY (item_id),
	   FOREIGN KEY (v_id) REFERENCES vendors
	);
	
	SELECT item_id, item_name, v.v_id, v.v_name
	FROM vendors v JOIN catalog c USING (v_id);

item_id	item_name	v_id	v_name
1	epitome	1	acme, inc.
2	ultimate	1	acme, inc.
3	definitive	3	solutionsystems, inc.
4	paradigm	2	bang.com

Suppose a new company purchased acme, inc. We would like to change the vendor id and name in the vendor table to reflect this.

	=# update vendors set v_id = 4, v_name='newco, inc.' where v_id=1;
	ERROR:  update or delete on "vendors" violates foreign key constraint "$1" on "catalog"
	DETAIL:  Key (v_id)=(1) is still referenced from table "catalog".

If we'd defined the catalog table like this:

	create table catalog (
	   item_id integer,
	   v_id integer,
	   item_name text,
	   PRIMARY KEY (item_id),
	   FOREIGN KEY (v_id) REFERENCES vendors
			ON UPDATE CASCADE
	);

item_id	item_name	v_id	v_name
1	epitome	4	newco, inc.
2	ultimate	4	newco, inc.
3	definitive	3	solutionsystems, inc.
4	paradigm	2	bang.com

Now suppose that solutionsystems went the way of the dot coms and is no longer in business. If we try to delete solutionsystems from the vendor table this is what happens:

 
	=# delete from vendors where v_id=3;
	ERROR:  update or delete on "vendors" violates foreign key constraint "$1" on "catalog"
	DETAIL:  Key (v_id)=(3) is still referenced from table "catalog".

	create table catalog (
	   item_id integer,
	   v_id integer,
	   item_name text,
	   PRIMARY KEY (item_id),
	   FOREIGN KEY (v_id) REFERENCES vendors
	      ON UPDATE CASCADE ON DELETE CASCADE
	);

item_id	item_name	v_id	v_name
1	epitome	4	newco, inc.
2	ultimate	4	newco, inc.
4	paradigm	2	bang.com

Not all FOREIGN KEYS should CASCADE. Most of the time you do not want them to. It should be a relatively rare case where you are updating a primary key in the first place. If you find yourself updating primary keys on a regular basis, perhaps you should rethink your choice of keys. The UPDATE example above is not a compelling example of why to change primary keys. The same effect could have been achieved by leaving the primary key alone and only updating the company name. A more compelling example might be when an email address is used as a primary key and the email address needs to be changed. However, it might still be more prudent to choose a primary key that does not need to be updated.

Another reason to avoid updating primary keys is to maintain history with your data. You may have an obsolete vendor in your database, but if it is marked as such, then you have the historical reference that the vendor was once there. This is important for financial and historical records, for example.

Contributors: beyaRecords uzo at beya-records.com, Stephan Szabo sszabo at megazone.bigpanda.com, Richard Huxton dev at archonet.com, elein at varlena.com

Querying Table Structure [GENERAL] Are there commands to enquire about table structure? 30-Jan-2004

There was a question as to how to query the table structure of PostgreSQL tables. This can be done using the new information_schema or via the existing pg_catalogs. Most of the time a \d in psql would be enough, but not always.

The questioner wanted column names in a table along with types. The following query provides a little bit more than that, so edit to your own needs. Optional conditions include querying on column_name and schema_name in addition or instead of the table_name query shown.

The SQL which defines this table is:

	create table lotsotypes (
	   aaa   integer,
	   bbb   text,
	   ccc   float(10),
	   ddd   double precision,
	   eee   int8,
	   fff   Point,
	   ggg   char(3),
	   hhh   varchar(17),
	   iii   oid,
	   jjj   numeric,
	   kkk   numeric(12,2),
	   lll   date,
	   mmm   time,
	   nnn   timestamptz,
	   ooo   timestamp(2),
	   ppp   boolean,
	   qqq   bytea,
	   rrr   inet,
	   sss   interval,
	   ttt   serial
	);

	select
	   ordinal_position as "Pos",
	   column_name as "Col",
	   data_type as "Type",
	   coalesce(character_maximum_length, numeric_precision_radix,
	   datetime_precision) as "Prec",
	   is_nullable as "Nullable",
	   column_default as "Default",
	   udt_name as "Built-In"
	from columns where table_name = 'lotsotypes'
	order by ordinal_position;

Pos	Col	Type	Prec	Nullable	Default	Built-In
1	aaa	integer	2	YES		int4
2	bbb	text		YES		text
3	ccc	real	2	YES		float4
4	ddd	double precision	2	YES		float8
5	eee	bigint	2	YES		int8
6	fff	point		YES		point
7	ggg	character	3	YES		bpchar
8	hhh	character varying	17	YES		varchar
9	iii	oid		YES		oid
10	jjj	numeric	10	YES		numeric
11	kkk	numeric	10	YES		numeric
12	lll	date		YES		date
13	mmm	time without time zone		YES		time
14	nnn	timestamp with time zone		YES		timestamptz
15	ooo	timestamp without time zone	2	YES		timestamp
16	ppp	boolean		YES		bool
17	qqq	bytea		YES		bytea
18	rrr	inet		YES		inet
19	sss	interval		YES		interval
20	ttt	integer	2	NO	nextval('public.lotsotypes_ttt_seq'::text)	int4

(20 rows)

A brief parsing of the \d queries in psql enabled me to put together this basically equivalent query. (To see the queries issued by psql start it using the -E option.)

	SELECT
	rel.nspname, rel.relname, attrs.attname, "Type", "Default", attrs.attnotnull
	FROM
	   (SELECT c.oid, n.nspname, c.relname
	    FROM pg_catalog.pg_class c
	    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
	    WHERE pg_catalog.pg_table_is_visible(c.oid)
	   ) rel
	JOIN 
	   (SELECT a.attname, a.attrelid,
	    pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
	    (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
	    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
	    as "Default", a.attnotnull, a.attnum
	    FROM pg_catalog.pg_attribute a
	    WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs
	 ON (attrs.attrelid = rel.oid )
	WHERE relname ~ '^lotsotypes$'
	ORDER BY attrs.attnum;

(20 rows)

Contributors: Ben reply at to-the-newsgroup.com, Lee Harr missive at hotmail.com, elein at varlena.com