pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Finding primary keys
2+ messages / 2 participants
[nested] [flat]

* Finding primary keys
@ 2000-04-10 22:37 Matlack, Brad <matlackb@ogden.disa.mil>
  2000-04-10 23:35 ` Re: Finding primary keys Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Matlack, Brad @ 2000-04-10 22:37 UTC (permalink / raw)
  To: pgsql-sql

I'm trying to determine which fields in a table are primary keys, using a
select statement.  

Can anyone help me with this?
Thanks,
Brad



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

* Re: Finding primary keys
  2000-04-10 22:37 Finding primary keys Matlack, Brad <matlackb@ogden.disa.mil>
@ 2000-04-10 23:35 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2000-04-10 23:35 UTC (permalink / raw)
  To: Matlack, Brad <matlackb@ogden.disa.mil>; +Cc: pgsql-sql

"Matlack, Brad" <matlackb@ogden.disa.mil> writes:
> I'm trying to determine which fields in a table are primary keys, using a
> select statement.  

This was just discussed in connection with the ODBC driver.  The best
solution I saw was

select ta.attname, ia.attnum
from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i
where c.relname = 'foo_pkey'
    AND c.oid = i.indexrelid
    AND ia.attrelid = i.indexrelid
    AND ta.attrelid = i.indrelid
    AND ta.attnum = i.indkey[ia.attnum-1]
ORDER BY ia.attnum;

where 'foo' is the table you are interested in.

You could also do just

select attname, attnum from pg_attribute a, pg_class c
where c.relname = 'foo_pkey'
    AND attrelid = c.oid
    AND attnum > 0
ORDER BY attnum;

but this'll not track renaming of columns via ALTER TABLE RENAME COLUMN,
since what it's showing you is actually the names of the columns in the
index relation, and ALTER doesn't bother to change those.

			regards, tom lane



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


end of thread, other threads:[~2000-04-10 23:35 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-10 22:37 Finding primary keys Matlack, Brad <matlackb@ogden.disa.mil>
2000-04-10 23:35 ` Tom Lane <tgl@sss.pgh.pa.us>

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