Re: query to select the table primary key column name

Uwe

Thank you very much for your help. That was exactly where I looked for.

Best regards
 
Dr. Horst Düster
GIS-Koordinator, Stv. Amtsvorsteher

Kanton Solothurn 
Amt für Geoinformation
Abteilung SO!GIS Koordination
Rötistrasse 4
CH-4501 Solothurn

Tel.: ++41 (0)32 627 25 32
Fax: ++41 (0)32 627 22 14

horst(dot)duester(at)bd(dot)so(dot)ch
www.sogis.so.ch

> -----Ursprüngliche Nachricht-----
> Von: Uwe C. Schroeder [mailto:uwe(at)oss4u(dot)com]
> Gesendet am: Dienstag, 31. Januar 2006 19:29
> An: pgsql-admin(at)postgresql(dot)org
> Cc: Duster Horst; 'pgsql-admin(at)postgresql(dot)org'
> Betreff: Re: [ADMIN] query to select the table primary key column name
>
> Check the information_schema views. Particularly you want to look into
> information_schema.key_column_usage and
> information_schema.table_contraints
>
> The later gives you the constraint type (in your case
> 'PRIMARY KEY') and the
> constraint name which you then can match to the constraint name in
> key_column_usage to get all the columns that make up the primary key.
>
> Hope that helps.
>
> BTW: using the information schema is the bettwe way to go for
> system catalog
> queries. Every major release will potentially have changes in
> the system
> catalogs, where the views in the information schema won't
> change a lot.
> They're designed to give you a stable API for the system catalogs.
>
>
> On Tuesday 31 January 2006 08:48, Düster Horst wrote:
> > I'm looking out for a system catalog query to select the
> primary key column
> > name for a specific table. With the following query I only
> get the name of
> > the primary key itself. How do I get the primary key column name???
> >
> > select pg_constraint.*,pg_index.indisprimary
> > from pg_constraint,pg_namespace, pg_class, pg_index
> > where pg_namespace.nspname='public'
> > and pg_namespace.oid=c.connamespace
> > and pg_class.relname='new_layer'
> > and pg_class.oid=c.conrelid
> > and pg_class.oid=pg_index.indrelid
> > and c.contype='p'
> >
> > I'll appeciate any help.
> >
> > with best regards
> >
> > Dr. Horst Düster
> >
> > Kanton Solothurn 
> > Amt für Geoinformation
> > Abteilung SO!GIS Koordination
> > Rötistrasse 4
> > CH-4501 Solothurn
> >
> > Tel.: ++41 (0)32 627 25 32
> > Fax: ++41 (0)32 627 22 14
> >
> > horst(dot)duester(at)bd(dot)so(dot)ch
> > www.sogis.so.ch
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> --
> UC
>
> --
> Open Source Solutions 4U, LLC 1618 Kelly St
> Phone: +1 707 568 3056 Santa Rosa, CA 95401
> Cell: +1 650 302 2405 United States
> Fax: +1 707 568 6416
>