Re: how to save primary key constraints

On 10/11/11 4:24 PM, J.V. wrote:
> pg_catalog table does not exist.
>
> This is a solution for PostgreSQL 8.4.

pg_catalog is a schema that has about 150 views and tables in it.

pg_tables is one such, as is pg_indexes (these two are both views)

you do realize, the primary key might not BE a field?  it could easily
be an expression, or multiple fields.


this will list all non-catalog tables and any indexes they have.

     select t.schemaname||'.'||t.tablename as name, i.indexname as
index, i.indexdef
         from pg_tables t left outer join pg_indexes i
             using (schemaname, tablename)
         where t.schemaname not in ('pg_catalog', 'information_schema');

it doesn't identify the primary index, except via the _pkey in the name,
however.

the pg_indexes view doesn't include the "indisprimary" boolean field of
pg_index, so you'd need to expand that view, and I'm too tired to think
that clearly right now.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast