Re: table column information

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: "Scot L(dot) Harris" <webid(at)cfl(dot)rr(dot)com>
Cc: pgsql-php(at)postgresql(dot)org
Subject: Re: table column information
Date: 2004-05-17 10:26:22
Message-ID: 1084789582.27913.7.camel@lamb.mcmillan.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-php

On Sun, 2004-05-16 at 23:16 -0400, Scot L. Harris wrote:
> >
> > You can also select the column names from the database metadata
> > directly:
> >
> > SELECT attname
> > FROM pg_class c join pg_attribute a on c.oid = a.attrelid
> > WHERE c.relname = '<your table name>'
> > AND a.attnum >= 0;
> >
> > This approach won't get killed by the efficiency problems above.
> >
> > Cheers,
> > Andrew.
>
> Thanks. Most of the tables I have are fairly small (for now) but at
> least one of them has many thousands of rows and I did not want to have
> to scan all of them for this information. I understand why the 0=1
> trick will scan every row. I like the idea of getting the meta data
> directly.
>
> None of the books I have seem to discuss this kind of thing. Is the
> pg_class and pg_attribute tables hidden? I see pga_layout and some
> others but not the first two when I do a \d. I do get a column listing
> when I do a \d pg_class so they are there.
>
> And this worked great on my test database/tables.

When I want to figure out something like this I tend to use "psql -E" so
that all queries are echoed before being sent to the backend. Then I do
something like "\d <table>" and see what SQL psql generates internally.

Also, dig here for detailed information on the postgresql data
dictionary tables:

http://www.postgresql.org/docs/7.4/interactive/catalogs.html

the most useful ones are pg_class and pg_attribute usually (for obvious
reasons :-). With 7.4 I also find myself looking at the
pg_stat_activity view from time to time as well.

Cheers,
Andrew.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
You possess a mind not merely twisted, but actually sprained.
-------------------------------------------------------------------------

In response to

Responses

Browse pgsql-php by date

  From Date Subject
Next Message Paul Lynch 2004-05-17 12:21:00 Re: Parsing Data, Table to Form
Previous Message Scot L. Harris 2004-05-17 03:16:15 Re: table column information