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. -------------------------------------------------------------------------
