The system catalogs of PostgreSQL have 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.
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…