Sender: charlus@ruault1.planetweb.com
Message-ID: <3651CF76.BDD6819C@planetweb.com>
Date: Tue, 17 Nov 1998 19:33:10 +0000
From: Charles-Edouard Ruault <cruault@planetweb.com>
Organization: PlanetWeb
MIME-Version: 1.0
To: pgsql-interfaces@postgresql.org
Subject: [JDBC Problem] unable to get primary keys through
	DatabaseMetaData.getPrimaryKeys()
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi all,

i just compiled & installed postgresql 6.4 and im trying to use the JDBC
driver ( the version provided with postgres source code ).
I've created a few tables, each has a primary key. I'm trying to get the
primary keys through the jdbc interface, using
DatabaseMetaData.getPrimaryKeys() but it always returns an empty
ResultSet.
I've looked at the source of the driver and it issues a cryptic ( to me
! ) sql request to retrieve the keys. I've tried to issue the same
request with pqsl and i also get an empty result. Is the request
incorrect ?

For example, here's the creation table script :

drop table ADDR;
drop sequence addr_id_seq;

CREATE TABLE ADDR (
 ID SEQUENCE PRIMARY KEY,
 OWNER int NOT NULL ,
 NAME char (96) NOT NULL ,
 EMAIL char (128) NOT NULL ,
 NOTIF int NOT NULL
);

and here's the sql request used by the driver :

SELECT '' as TABLE_CAT, '' AS TABLE_SCHEM,
           bc.relname AS TABLE_NAME,
           ic.relname AS COLUMN_NAME,
           '1' as KEY_SEQ,
           t.typname as PK_NAME
           FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a,
pg_type t
           WHERE bc.relkind = 'r'
           and bc.relname ~ 'ADDR'
           and i.indrelid = bc.oid
           and i.indexrelid = ic.oid
           and i.indkey[0] = a.attnum
           and i.indproc = '0'::oid
           and a.attrelid = bc.oid
           ORDER BY TABLE_NAME, COLUMN_NAME;

Thanks for your suggestions/help.