Re: column names, types, properties for a table

OK, I found the solution ( after a little bit of research and testing :) )

Does anyone have recommendations regarding the following query ?

SELECT pg_attribute.attname, pg_attribute.attnotnull,
pg_attribute.atthasdef, pg_type.typname, pg_attrdef.adsrc AS
default_value, pg_constraint.contype, pg_constraint.conname
FROM pg_attribute
INNER JOIN pg_class ON (pg_class.oid = pg_attribute.attrelid AND
pg_class.relkind = 'r')
INNER JOIN pg_type ON (pg_type.oid = pg_attribute.atttypid AND
pg_type.typname NOT IN ('oid', 'tid', 'xid', 'cid'))
LEFT JOIN pg_attrdef ON (pg_attrdef.adrelid = pg_attribute.attrelid AND
pg_attrdef.adnum = pg_attribute.attnum)
LEFT JOIN pg_constraint ON (pg_constraint.conrelid = pg_attribute.attrelid
AND (pg_constraint.conkey[1] = pg_attribute.attnum OR
pg_constraint.conkey[2] = pg_attribute.attnum OR pg_constraint.conkey[3] =
pg_attribute.attnum OR pg_constraint.conkey[4] = pg_attribute.attnum OR
pg_constraint.conkey[5] = pg_attribute.attnum OR pg_constraint.conkey[6] =
pg_attribute.attnum) OR pg_constraint.conkey[7] = pg_attribute.attnum OR
pg_constraint.conkey[8] = pg_attribute.attnum)
WHERE pg_class.relname = 'sip_groupe_sanguin';

Best Regards,
Roger Tannous.

--- Philip Hallstrom <postgresql(at)philip(dot)pjkh(dot)com> wrote:

> > Is it possible to issue an SQL query that lists column names, types
> (int,
> > varchar, boolean, etc.), properties (like NOT NULL or UNIQUE)
> > for a given table name ?
>
> Start psql with the -E option. Then "\d yourtable". It will print out
> the queries that are run internally to show you the table info... for
> example:
>
> % psql -E cc_8004
> Welcome to psql 7.4.2, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> cc_8004=# \d rep_general;
> ********* QUERY **********
> SELECT c.oid,
> n.nspname,
> c.relname
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE pg_catalog.pg_table_is_visible(c.oid)
> AND c.relname ~ '^rep_general$'
> ORDER BY 2, 3;
> **************************
>
> ********* QUERY **********
> SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
> FROM pg_catalog.pg_class WHERE oid = '21548032'
> **************************
>
> ********* QUERY **********
> SELECT a.attname,
> pg_catalog.format_type(a.atttypid, a.atttypmod),
> (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
> WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
> a.atthasdef),
> a.attnotnull, a.attnum
> FROM pg_catalog.pg_attribute a
> WHERE a.attrelid = '21548032' AND a.attnum > 0 AND NOT a.attisdropped
> ORDER BY a.attnum
> **************************
>
> ********* QUERY **********
> SELECT c2.relname, i.indisprimary, i.indisunique,
> pg_catalog.pg_get_indexdef(i.indexrelid)
> FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index
> i
> WHERE c.oid = '21548032' AND c.oid = i.indrelid AND i.indexrelid =
> c2.oid
> ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
> **************************
>
> ********* QUERY **********
> SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
> WHERE c.oid=i.inhparent AND i.inhrelid = '21548032' ORDER BY inhseqno
> ASC
> **************************
>
> Table "public.rep_general"
> Column | Type | Modifiers
>
-------------------------+-----------------------------+--------------------
> id | integer | not null
> loc_id | integer | not null
> dt | timestamp without time zone | not null
> num_active_visits | integer | not null
> default 0
> num_passive_visits | integer | not null
> default 0
> min_visit_length | integer | not null
> default 0
> max_visit_length | integer | not null
> default 0
> total_visit_length | integer | not null
> default 0
> total_time_before_touch | integer | not null
> default 0
> total_time_of_touch | integer | not null
> default 0
> num_coupons_printed | integer | not null
> default 0
> num_passive_promos | integer | not null
> default 0
> num_active_promos | integer | not null
> default 0
> Indexes:
> "rep_general_pk" primary key, btree (id)
> "rep_general_dt_idx" btree (dt)
> "rep_general_loc_id_idx" btree (loc_id)
>
> cc_8004=#
>
>

______________________________________________________
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/