On Sun, 2004-05-16 at 22:55, Andrew McMillan wrote: > On Mon, 2004-05-17 at 00:19 +0000, ljb wrote: > > > But I don't think I want to select the entire contents of the table > > > every time I want to get the names of the columns. I know this will > > > work but I think performance will be very poor. > > >... > > > > You almost got it - just do "select * from tablename where 0=1", which returns > > no rows but will give you the fieldnames. A portable and (I think) > > efficient way to get table column names. > > It can be a cute trick, and I use it myself from time to time > (especially for "CREATE TABLE AS SELECT ..." where I want an empty table > with the same structure, pre v 7.4 which can do this anyway). You > should be aware however that as written above it will almost invariably > force a full-table scan! > > 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. Thanks! -- Scot L. Harris <webid@xxxxxxxxxx>
