Re: how to save primary key constraints

On 10/11/2011 05:16 PM, J.V. wrote:
> I need to be able to query for all primary keys and save the table name
> and the name of the primary key field into some structure that I can
> iterate through later.
>
> How would I go about this? I want to hard code the number of tables and
> be able to iterate through some structure to get the table name and the
> primary key field.

A query such as the following may help:

SELECT nspname, conrelid::regclass::name, conname
FROM pg_constraint c
JOIN pg_namespace ON (connamespace = pg_namespace.oid)
LEFT JOIN pg_class on (conname = relname)
WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema')
AND contype = 'p'
ORDER BY nspname, 2, conname;

The first column is the schema name, the second the table name and the
third the constraint (primary key) name.

Joe