pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcolumn names, types, properties for a table
4+ messages / 3 participants
[nested] [flat]
* column names, types, properties for a table
@ 2005-09-07 21:25 Roger Tannous <roger77_lb@yahoo.com>
2005-09-07 21:38 ` Re: column names, types, properties for a table Philip Hallstrom <postgresql@philip.pjkh.com>
2005-09-08 20:37 ` Re: column names, types, properties for a table thomas.silvi <thomas.silvi@laposte.net>
0 siblings, 2 replies; 4+ messages in thread
From: Roger Tannous @ 2005-09-07 21:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: column names, types, properties for a table
2005-09-07 21:25 column names, types, properties for a table Roger Tannous <roger77_lb@yahoo.com>
@ 2005-09-07 21:38 ` Philip Hallstrom <postgresql@philip.pjkh.com>
2005-09-07 23:08 ` Re: column names, types, properties for a table Roger Tannous <roger77_lb@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Philip Hallstrom @ 2005-09-07 21:38 UTC (permalink / raw)
To: Roger Tannous <roger77_lb@yahoo.com>; +Cc: pgsql-sql
> 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=#
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: column names, types, properties for a table
2005-09-07 21:25 column names, types, properties for a table Roger Tannous <roger77_lb@yahoo.com>
2005-09-07 21:38 ` Re: column names, types, properties for a table Philip Hallstrom <postgresql@philip.pjkh.com>
@ 2005-09-07 23:08 ` Roger Tannous <roger77_lb@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Roger Tannous @ 2005-09-07 23:08 UTC (permalink / raw)
To: Philip Hallstrom <postgresql@philip.pjkh.com>; +Cc: 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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: column names, types, properties for a table
2005-09-07 21:25 column names, types, properties for a table Roger Tannous <roger77_lb@yahoo.com>
@ 2005-09-08 20:37 ` thomas.silvi <thomas.silvi@laposte.net>
1 sibling, 0 replies; 4+ messages in thread
From: thomas.silvi @ 2005-09-08 20:37 UTC (permalink / raw)
To: Roger Tannous <roger77_lb@yahoo.com>; +Cc: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-09-08 20:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-07 21:25 column names, types, properties for a table Roger Tannous <roger77_lb@yahoo.com>
2005-09-07 21:38 ` Philip Hallstrom <postgresql@philip.pjkh.com>
2005-09-07 23:08 ` Roger Tannous <roger77_lb@yahoo.com>
2005-09-08 20:37 ` thomas.silvi <thomas.silvi@laposte.net>
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