PostgreSQL Training,
Consulting & Support
General Bits
By A. Elein Mustain

02-Feb-2004 Issue: 60

Archives | General Tidbits | Google General Bits | Docs | Castellano | PortuguÍs | Subscriptions | Notifications | | Prev

General Bits is a column loosely based on the PostgreSQL mailing list pgsql-general.
To find out more about the pgsql-general list and PostgreSQL, see

Availability of Functions
[GENERAL] Creating 'global' functions. 31-Jan-2004

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, elein at
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);
1epitome1acme, inc.
2ultimate1acme, inc.
3definitive3solutionsystems, inc.

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
Then the same UPDATE from above would give us the folowing data:
1epitome4newco, inc.
2ultimate4newco, inc.
3definitive3solutionsystems, inc.
That is better. All of the referential integrity is intact.

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".
If we had defined the catalog table as:
	create table catalog (
	   item_id integer,
	   v_id integer,
	   item_name text,
	   PRIMARY KEY (item_id),
	   FOREIGN KEY (v_id) REFERENCES vendors
Then the DELETE from above would produce the following results:
1epitome4newco, inc.
2ultimate4newco, inc.

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, Stephan Szabo sszabo at, Richard Huxton dev at, elein at
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
The query executed is as follows. Notice that instead of casing the precision we used coalesce() since a type would either be character, numeric, or datetime and never two of those.
	   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;
The results show the built-in datatype that is used. This is always a synonym for the declared type except where the declared type is a domain.

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.)

	rel.nspname, rel.relname, attrs.attname, "Type", "Default", attrs.attnotnull
	   (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
	   (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;
nspname relname attname Type Default attnotnull
public lotsotypes aaa integer   f
public lotsotypes bbb text   f
public lotsotypes ccc real   f
public lotsotypes ddd double precision   f
public lotsotypes eee bigint   f
public lotsotypes fff point   f
public lotsotypes ggg character(3)   f
public lotsotypes hhh character varying(17)   f
public lotsotypes iii oid   f
public lotsotypes jjj numeric   f
public lotsotypes kkk numeric(12,2)   f
public lotsotypes lll date   f
public lotsotypes mmm time without time zone   f
public lotsotypes nnn timestamp with time zone   f
public lotsotypes ooo timestamp(2) without time zone   f
public lotsotypes ppp boolean   f
public lotsotypes qqq bytea   f
public lotsotypes rrr inet   f
public lotsotypes sss interval   f
public lotsotypes ttt integer nextval('public.lotsotypes_ttt_seq'::text) t

(20 rows)

The column that was left out was the underlying built-in datatype. That would require joining in the types one more time. This query also utilized the built-in function pg_catalog.format_type() to put the type name together correctly.

Contributors: Ben reply at, Lee Harr missive at, elein at

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

Search General Bits & Search WWW