From: "D'Arcy" "J.M." Cain <darcy@druid.net>
To: olly@lfix.co.uk
Cc: darcy@druid.net
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
Date: Sat, 20 Feb 1999 08:18:52 -0500 (EST)
Message-ID: <m10ECJ2-0000boC@druid.net> (raw)
In-Reply-To: <199902200832.IAA09157@linda.lfix.co.uk>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, olly@lfix.co.uk
Subject: Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
In-Reply-To: <m10ECJ2-0000boC@druid.net>
References: <199902200832.IAA09157@linda.lfix.co.uk> <m10ECJ2-0000boC@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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