To list all the pk’s and fk’s

The system catalogs of PostgreSQL are very well designed. They are not easy to understand at first glance but they allow to write short and efficient queries.

As usual, if the data model is sound, the queries are simple and natural.

Just an example : the comma separated list of columns for composite keys is automatically created in this query :

select
     conrelid::regclass as table_name
    ,conname
    ,pg_get_constraintdef(c.oid)
from
    pg_constraint c
join
    pg_namespace n on n.oid = c.connamespace
where
    contype in ('f', 'p')
and
    n.nspname = 'public'
order by
    conrelid::regclass::text
   ,contype desc;

The output is like that :

...
"SLM_TYPE"           SLM_TYPE_pkey                   PRIMARY KEY ("SLM_TYPE_NR")
"SLM_TYPE_CONTACT"   SLM_TYPE_CONTACT_CONTACT_fkey   FOREIGN KEY ("CONTACT") 
                                                        REFERENCES "CONTACTS"("CONTACT_NR") 
                                                        ON DELETE CASCADE
...

By the way, it is always better to use serials for pk’s and fk’s. But I am working with database I migrate from Access to PostgreSQL. I would like to write a script that will add serials for those pk’s and fk’s and that will transform the initial pk’s and fk’s to unique constraints. Not so easy…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s