From: Christoph Haller <ch@rodos.fzk.de>
Message-Id: <200311111456.PAA16390@rodos>
Subject: Re: How to know column constraints via system catalog tables
To: so_excited@excite.com
Date: Tue, 11 Nov 2003 15:56:19 MET
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20031111131607.E97943DE6@xmxpita.excite.com>;
	from "Damon Chong" at Nov 11, 103 2:16 pm

> 
> Thanks for the reply but my main problem is I'm trying to find the primary column(s) and the data type of these column in 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 and its 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
ELSE
 0 
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.attrelid and 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