PostgreSQL Tricks

Over the last year or so I’ve gathered a little trick bag of PostgreSQL recipes. Here are some of the best.

These all work in PostgreSQL version 8.4.7 and probably most other versions. The “–” below are PostgreSQL comments.

To see your PostgreSQL version, use:

select version();

It’s sometimes useful to be able to discover the tables in a schema automatically. To do this you can use the following command:

>>> \dt my_schema.* — here the * is a wildcard

Or this command, which uses the PostgreSQL internal tables pg_class and pg_namespace:

>>> select n.nspname, c.relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=’my_schema’;

Where “my_schema” is a schema name. Once you’ve found the table you’re interested in, \d table_name gives you the columns and their types.

To discover the columns in a table (that is in a schema):

>>> select a.attname from pg_class as c, pg_namespace as n, pg_attribute as a where n.oid=c.relnamespace and n.nspname=’my_schema’ and c.relname=’my_table’ and a.attrelid=c.oid;

Where “my_table” is the table name in your schema. This uses the PostgreSQL internal tables pg_class, pg_namespace and pg_attribute.

To discover the type of a column in a table (that is in a schema)

>>> select a.attname, t.typname, a.atttypid from pg_class as c, pg_namespace as n, pg_attribute as a, pg_type as t where n.oid=c.relnamespace and n.nspname=’my_schema’ and c.relname=’my_table’ and a.attrelid=c.oid and a.attname=’my_column’ and t.oid=a.atttypid;

Where “my_column” is the column name in the table. This is similar to the previous command, but also uses internal table pg_type.

If you have some duplicate rows in your table, you can delete them using the hidden ctid column, e.g.:

>>> select ctid, * from my_table; — show me the repeats

>>> delete from my_table where CAST(“ctid” as text) like ‘%3%’; — kill some according to some wildcard pattern

A more automatic way is:

>>> delete form my_table where ctid not in (select max(dup.ctid) from my_table as dup group by dup.x, dup.y, dup.z); — substitute appropriate column names for x, y, z

Allowable formats for dates are controlled by the PostgreSQL setting ‘datestyle’. To see it, type:

show datestyle;

To change the datestyle you can use (for example):

set datestyle to ‘iso, dmy’;

But note that this change is not a permanent change (it only applies to this session/cursor).

Lastly, I sometimes use this command:

table my_table;

as a shorthand for select * from my_table.