Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key

Thus spake Oliver Elphick
>   >> how can I ask postgres which is the primary key from table test?
>   >
>   >SELECT  pg_class.relname, pg_attribute.attname
>   >    FROM pg_class, pg_attribute, pg_index
>   >    WHERE pg_class.oid = pg_attribute.attrelid AND
>   >        pg_class.oid = pg_index.indrelid AND
>   >        pg_index.indkey[0] = pg_attribute.attnum AND
>   >        pg_index.indisprimary = 't';
>   >
>   >That lists all the primary keys in your database.  Add a "WHERE pg_class
>   >= 'test'" clause to get the specific table.
>
> I tried this and got 0 rows.  Every value of pg_index.indisprimary is
> false, although I have nearly 100 tables created with primary keys.
> (This is with 6.4.2)

Oops.  Full primary key support is in current and will be in the next
release.  You can set these fields yourself manually to allow this
query to work.  Here is a macro I use in a scripting language I wrote
which does this given a table and a field.  It should give you the idea
of how to do this.

@DEFINE    KEY    UPDATE pg_index SET indisprimary = 't'\
                      WHERE pg_index.oid in (SELECT pg_index.oid\
   FROM pg_class, pg_attribute, pg_index\
   WHERE pg_class.oid = pg_attribute.attrelid AND\
       pg_class.oid = pg_index.indrelid AND\
       pg_index.indkey[0] = pg_attribute.attnum AND\
       pg_class.relname = '$0' AND \
       pg_attribute.attname = '$1');

Unfortunately, that still leaves you with the problem of figuring
out which fields are primary the first time.

Once you have moved to current, this will be done automatically for
you when you declare a field to be the primary key.

--
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.