From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Matlack, Brad" <matlackb(at)ogden(dot)disa(dot)mil> |
Cc: | "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Finding primary keys |
Date: | 2000-04-10 23:35:12 |
Message-ID: | 26622.955409712@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
"Matlack, Brad" <matlackb(at)ogden(dot)disa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Perrin - Demography | 2000-04-10 23:45:22 | Using overlaps() |
Previous Message | Tom Lane | 2000-04-10 23:25:35 | Re: Speedy query help.. |