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

"D'Arcy" "J.M." Cain wrote:
  >Thus spake Marc Grimme
  >> if I create a table like this:
  >> CREATE TABLE test (
  >>    id decimal(3) primary key,
  >>    name varchar(32));
  >>
  >> 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)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "For the Lord himself shall descend from heaven with a
      shout, with the voice of the archangel, and with the
      trump of God; and the dead in Christ shall rise first;
      Then we which are alive and remain shall be caught up
      together with them in the clouds, to meet the Lord in
      the air; and so shall we ever be with the Lord."
                          I Thessalonians 4:16,17

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.