Re: How do system tables relate to other tables in postgresql

On Wed, Jul 11, 2001 at 02:06:31PM +0200, Graeme Longman wrote:
> I'm trying to write a select statement which returns the all the columns of
> a table with a 'not null' modifier.
>
> I realise that I will need to use the system tables but can't work out which
> columns of which system tables to include in my statement.

best way i've seen to learn about the system tables in general,
is start psql with "-E" to echo system queries generated by \d
shortcuts:

$ psql -E mydb
mydb=# \d _who

********* QUERY *********
SELECT usesuper FROM pg_user WHERE usename = 'will'
*************************

********* QUERY *********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='_who'
*************************

********* QUERY *********
SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = '_who'
AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
ORDER BY a.attnum
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 1
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 2
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 3
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 4
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 5
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 6
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 7
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 8
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 10
*************************

********* QUERY *********
SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
WHERE c.relname = '_who' AND c.oid = d.adrelid AND d.adnum = 11
*************************

********* QUERY *********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = '_who' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY c2.relname
*************************

Table "_who"
Attribute | Type | Modifier
-----------+-------------+-----------------------------------------------
a | integer | default 0
b | integer | default 0
c | integer | default 0
d | integer | default 0
f | integer | default 0
tot | integer | default 0
created | timestamp | default "timestamp"('now'::text)
modified | timestamp | default "timestamp"('now'::text)
editor | integer |
status | char(1) | default 'U'
id | integer | not null default nextval('_who_id_seq'::text)
login | varchar(40) | not null
password | varchar(20) |
hint | varchar(40) |
name | varchar(40) | not null
email | varchar(40) |
Indices: _who_id_key,
_who_pkey

as you can tell, there are still some behind-the-scenes magic to
turn some of those select results into 'Modifier' strings...

--
I'd concentrate on "living in the now" because it is fun
and on building a better world because it is possible.
- Tod Steward

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!