Обсуждение: column names, types, properties for a table

Поиск
Список
Период
Сортировка

column names, types, properties for a table

От
Roger Tannous
Дата:
Hi, 

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 ?


Regards,
Roger Tannous.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

Re: column names, types, properties for a table

От
Philip Hallstrom
Дата:
> 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_attrdefd   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                      |
timestampwithout 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=#

Re: column names, types, properties for a table

От
Roger Tannous
Дата:
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/

Re: column names, types, properties for a table

От
"thomas.silvi"
Дата:
          Hello,   there is the view "columns" in the schema "information_schema" that 
can give you most of the informations you need   ( for PosgreSQL version >= 7.4.8 if I'm right).
   SELECT   *   FROM     information_schema.columns   WHERE    table_name = 'mytable';
   See 
http://www.postgresql.org/docs/8.0/interactive/infoschema-columns.html#AEN26185     or  
http://www.postgresql.org/docs/8.0/static/infoschema-columns.html#AEN26185
      Regards,            Thomas

Roger Tannous a écrit :

>Hi, 
>
>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 ?
>
>
>Regards,
>Roger Tannous.
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around 
>http://mail.yahoo.com 
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>  
>