After a very long time, I decided it was time to rewrite a script I created so I could move databases from one version of PostgreSQL to another. For instance, I could move a PostgreSQL 8.3 database to any version on PostgreSQL after 7.3. The problem was, I never got around to implementing constraints in it. Hopefully someone can find this info useful.

Constraint information is held in the pg_constraint table. The column, conrelid, defines the relation id of the table the constraint is on, and it corresponds to the relfilenode column in pg_class. This column is used for the join.

Constraint type is held in the contype column of the pg_constraint table. Here’s a map of the different types:

• f = Foreign Key
• p = Primary Key
• u = Unique
• c = Check Constraint

The consrc column holds information about check constraints, so for instance, we can do:

SELECT
    relname,
    conname,
    consrc
    FROM pg_constraint, pg_class
    WHERE conrelid = relfilenode AND contype = 'c';

relname     |  conname                     | consrc
---------------------------------------------------------------------
ratings      | ratings_chk_rating    | ((rating >= 1) AND (rating <= 5))

(1 row)

Primary Keys and Unique constraints are a bit more complex, as you have to find the column name as well. You can do something like this (Note: this won’t work for multi-column primary/unique keys):

SELECT
    relname,
    attname,
    conname,
    CASE WHEN contype = 'p' THEN 'primary' ELSE 'unique' END AS type
    FROM pg_constraint
        INNER JOIN pg_class ON (conrelid = relfilenode)
        INNER JOIN pg_attribute ON (conrelid = attrelid AND conkey[1] = attnum)
    WHERE contype = 'u' OR contype = 'p';

Will return something like:

relname | attname | conname       | type
--------------------------------------------
stores    | id             | stores_pkey | primary

Foreign Keys are even more complex, as you have to join pg_attribute and pg_class twice:

SELECT
    c1.relname AS local_table,
    c2.relname AS foreign_table,
    conname,
    a1.attname AS local_key,
    a2.attname AS foreign_key,
    condeferrable
    FROM pg_constraint
        INNER JOIN pg_class c1 ON (conrelid = c1.relfilenode)
        INNER JOIN pg_class c2 ON (confrelid = c2.relfilenode)
        INNER JOIN pg_attribute a1 ON (conrelid = a1.attrelid AND conkey[1] = a1.attnum)
        INNER JOIN pg_attribute a2 ON (confrelid = a2.attrelid AND confkey[1] = a2.attnum)
    WHERE contype = 'f';

This entry was posted on Friday, March 28th, 2008 at 11:52 am and is filed under PostgreSQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.