Thread: How to know column constraints via system catalog tables
Hi, I need to query each column's constraint and name of a table in postgreSQL v7.3.4 with a single SQL query but don't know how. Would appreciate any pointers! Thank you. Regards, Damon
> > Hi, > > I need to query each column's constraint and name of a table in > postgreSQL v7.3.4 with a single SQL query but don't know how. Would > appreciate any pointers! > > Thank you. > > Regards, > Damon > select relname,conname from pg_constraint,pg_class where pg_class.oid=conrelid ; could be start. Regards, Christoph
>
> 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
Thanks alot, you are right with the table and the -E option. It's very useful.
The only issue I find with your SQL is it relies on "(a.attnum = x.conkey[1] or a.attnum = x.conkey[2])" which assumes
thereis two columns forming the primary key of a table. Perhaps, I should explain what I'm trying to achieve with this
SQL.
I'm writing an abstraction layer (abit like persistent but less ambitious). I hope to make this C++ layer generic for
anyRDBMS as long as the RDMS allow discovery of a table's columns name and the columns' data type. The RDBMS should
alsoexpose the field(s) used to form the primary key of a table. This will free the programmer from coding the class
datamember to correspond to the underlying table's fields (automate those tedious tasks of mapping OO classes to
databasetables).
I'm using libpqxx for postgreSQL, I had thought of a hack which is to strip the {1,2..} string returned by the conkey
ofpg_constraint to get the column numbers. It's not pretty as I have to execute at least two queries but it should be
workable.
Thanks.
---------------snip-------------------
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?
_______________________________________________
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
>
> The only issue I find with your SQL is it relies on "(a.attnum = x.conkey[1] or a.attnum = x.conkey[2])" which
assumesthereis two columns forming the primary key of a table. Perhaps, I should explain what I'm trying to achieve
withthisSQL.
>
Not exactly. Look at this
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)
);
CREATE TABLE films_02 ( code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY, title CHARACTER VARYING(40)
NOTNULL, did DECIMAL(3) NOT NULL, date_prod DATE, kind CHAR(10), len INTERVAL HOUR TO
MINUTE
);
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 like 'film%' 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 | 0CH | FILMS | TITLE | 1043 | 40 |
0 | 2 | 0CH | FILMS_02 | CODE | 1042 | 5 | 0 | 1 |
0
(3 rows)
My supposition was it's no sign of good design to form primary keys from more
than two columns.
As you can see primary keys on a single column will be listed as well.
So, if you want to deal with wider keys,
you can add "or a.attnum = x.conkey[3]" etc.
> I'm writing an abstraction layer (abit like persistent but less ambitious). I hope to make this C++ layer generic
for
any RDBMS as long as the RDMS allow discovery of a table's columns name and the columns' data type. The RDBMS should
als
o expose the field(s) used to form the primary key of a table. This will free the programmer from coding the class
data
member to correspond to the underlying table's fields (automate those tedious tasks of mapping OO classes to database
ta
bles).
Good Idea. But I've found it's always a PITA to question system tables,
no matter which RDBMS you're on.
> I'm using libpqxx for postgreSQL, I had thought of a hack which is to strip the {1,2..} string returned by the
conkey
of pg_constraint to get the column numbers. It's not pretty as I have to execute at least two queries but it should be
w
orkable.
Anyway.
Regards, Christoph