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 :

     conrelid::regclass as table_name
    pg_constraint c
    pg_namespace n on n.oid = c.connamespace
    contype in ('f', 'p')
    n.nspname = 'public'
order by
   ,contype desc;

The output is like that :

"SLM_TYPE"           SLM_TYPE_pkey                   PRIMARY KEY ("SLM_TYPE_NR")
                                                        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: Logo

You are commenting using your 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