pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
query to select the table primary key column name
3+ messages / 2 participants
[nested] [flat]

* query to select the table primary key column name
@ 2006-01-31 16:48 Düster Horst <Horst.Duester@bd.so.ch>
  2006-01-31 18:28 ` Re: query to select the table primary key column name Uwe C. Schroeder <uwe@oss4u.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Düster Horst @ 2006-01-31 16:48 UTC (permalink / raw)
  To: pgsql-admin

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.duester@bd.so.ch 
www.sogis.so.ch




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: query to select the table primary key column name
  2006-01-31 16:48 query to select the table primary key column name Düster Horst <Horst.Duester@bd.so.ch>
@ 2006-01-31 18:28 ` Uwe C. Schroeder <uwe@oss4u.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Uwe C. Schroeder @ 2006-01-31 18:28 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Düster Horst <Horst.Duester@bd.so.ch>; pgsql-admin

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.duester@bd.so.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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: query to select the table primary key column name
@ 2006-02-01 07:35 Düster Horst <Horst.Duester@bd.so.ch>
  0 siblings, 0 replies; 3+ messages in thread

From: Düster Horst @ 2006-02-01 07:35 UTC (permalink / raw)
  To: 'Uwe C. Schroeder' <uwe@oss4u.com>; pgsql-admin

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.duester@bd.so.ch 
www.sogis.so.ch



> -----Ursprüngliche Nachricht-----
> Von: Uwe C. Schroeder [mailto:uwe@oss4u.com]
> Gesendet am: Dienstag, 31. Januar 2006 19:29
> An: pgsql-admin@postgresql.org
> Cc: Duster Horst; 'pgsql-admin@postgresql.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.duester@bd.so.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
> 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-02-01 07:35 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-31 16:48 query to select the table primary key column name Düster Horst <Horst.Duester@bd.so.ch>
2006-01-31 18:28 ` Uwe C. Schroeder <uwe@oss4u.com>
2006-02-01 07:35 Re: query to select the table primary key column name Düster Horst <Horst.Duester@bd.so.ch>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox