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.