List prime key column names

Started by CNalmost 22 years ago2 messagesgeneral
Jump to latest
#1CN
cnliou9@fastmail.fm

Hi!

I am trying to retrieve the column names that comprise primary key of a
table.
According to manual, pg_index.indkey is an array of indnatts whose usage
I am unaware of.

CREATE TABLE table1 (
PRIMARY KEY (c1,c2),
c1 SMALLINT,
c2 VARCHAR(10),
c3 VARCHAR(10)
)WITHOUT OIDS;

I need a SQL to list the following 2 rows:

c1
c2

The following SQL fails with error "syntax error at or near "ANY" at
character 214":

select attname
from pg_class,pg_index,pg_attribute
where relname='table1' and relkind='r' and relhaspkey
and indrelid=pg_class.oid and indisprimary
and attrelid=pg_class.oid and attnum ANY (indkey)

Helps will be much appreciated!

Regards,
CN

#2CN
cnliou9@fastmail.fm
In reply to: CN (#1)
Re: List prime key column names

I am trying to retrieve the column names that comprise primary key of a
table.
According to manual, pg_index.indkey is an array of indnatts whose usage
I am unaware of.

CREATE TABLE table1 (
PRIMARY KEY (c1,c2),
c1 SMALLINT,
c2 VARCHAR(10),
c3 VARCHAR(10)
)WITHOUT OIDS;

I need a SQL to list the following 2 rows:

c1
c2

The following SQL:

select attname
from pg_class,pg_index,pg_attribute
where relname='table1' and relkind='r' and relhaspkey
and indrelid=pg_class.oid and indisprimary
and attrelid=pg_class.oid and attnum = ANY (indkey)

gives error: "ERROR: op ANY/ALL (array) requires array on right side"

Further tests show that this appears to work:

select attname
from pg_class,pg_index,pg_attribute
where relname='table1' and relkind='r' and relhaspkey
and indrelid=pg_class.oid and indisprimary
and attrelid=pg_class.oid and attnum IN
(indkey[0],indkey[1],indkey[2].....indkey[63])

But is there any shortcut?

Regards,
CN