>
> Thanks for the reply but my main problem is I'm trying to find the primary column(s) and the data type of these
columnin a table. pg_constraint's conkey is a int2[] field. So if i have a table called 'films' (taken from postgreSQL
doc)that has two primary keys (composite primary key) then the query below gives me {1,2} for column 1 and column 2.
>
> select x.conkey from
> pg_constraint x, pg_class a
> where a.relfilenode=x.conrelid
> and a.relname='films'
> and x.contype='p';
>
> I cannot match the above with the next query to get a single query statement that provides the primary column's name
andits data type.
>
> SELECT distinct c.attname FROM
> pg_class a, pg_constraint b,
> pg_attribute c
> WHERE a.relfilenode=c.attrelid
> AND c.attnum>0
> AND a.relname='films';
>
> Thanks for spending the time to answer my query!
>
>
First, do you know the psql -E option which shows you the SQL behind the \d outputs.
You have probably used this films table (there is more than one in the doc):
CREATE TABLE films (
code CHAR(5),
title VARCHAR(40),
did DECIMAL(3),
date_prod DATE,
kind VARCHAR(10),
len INTERVAL HOUR TO MINUTE,
CONSTRAINT code_title PRIMARY KEY(code,title)
);
Based on a query I'm using to retrieve column information
(improved by Tom Lane's help), I think I've found something useful. Try
SELECT
upper(u.usename) AS TBL_OWNER, upper(c.relname) AS TBL_NAME,
upper(a.attname) AS COL_NAME, a.atttypid AS COL_TYPE,
int4larger(a.attlen, a.atttypmod - 4) AS COL_LENGTH,
CASE WHEN a.attnotnull=true THEN 0 ELSE 1 END AS COL_NULL, a.attnum AS COL_SEQ,
CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d
WHERE d.adrelid = a.attrelid and d.adnum = a.attnum) THEN 1
ELSE0
END AS COL_DEFAULT
from pg_attribute a, pg_constraint x, pg_class c left join pg_user u on (u.usesysid = c.relowner) where c.oid =
a.attrelidand not (c.relname ~* 'pg_') and c.relkind = 'r' and a.attnum > 0 and c.relfilenode=x.conrelid and
x.contype='p'and c.relname ='films' and (a.attnum = x.conkey[1] or a.attnum = x.conkey[2]) ;
tbl_owner | tbl_name | col_name | col_type | col_length | col_null | col_seq | col_default
-----------+----------+----------+----------+------------+----------+---------+-------------CH | FILMS | CODE
| 1042 | 5 | 0 | 1 | 0 CH | FILMS | TITLE | 1043 | 40 |
0 | 2 | 0(2 rows)
You'll probably want to get rid of some parts (e.g. the upper case conversion),
but basically it's what you were looking for. Right?
Regards, Christoph
