Re: How do system tables relate to other tables in postgresql - Mailing list pgsql-general

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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!