From: Christoph Haller <ch@rodos.fzk.de>
To: so_excited@excite.com
Cc: pgsql-sql@postgresql.org
Subject: Re: How to know column constraints via system catalog tables
Date: Tue, 11 Nov 2003 15:56:19 MET
Message-ID: <200311111456.PAA16390@rodos> (raw)
In-Reply-To: <20031111131607.E97943DE6@xmxpita.excite.com>
>
> 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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, so_excited@excite.com
Subject: Re: How to know column constraints via system catalog tables
In-Reply-To: <200311111456.PAA16390@rodos>
* 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