Re: column names, types, properties for a table - Mailing list pgsql-sql

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@philip.pjkh.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/