From: Scot L. Harris <webid@cfl.rr.com>
To: pgsql-php@postgresql.org
Subject: Re: table column information
Date: Sun, 16 May 2004 23:16:15 -0400
Message-ID: <1084763775.2058.173.camel@lathe> (raw)
In-Reply-To: <1084762532.4822.36.camel@lamb.mcmillan.net.nz>
References: <1084749506.2056.148.camel@lathe>
<c890e8$12ql$1@news.hub.org>
<1084762532.4822.36.camel@lamb.mcmillan.net.nz>
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@cfl.rr.com>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: webid@cfl.rr.com
Subject: Re: table column information
In-Reply-To: <1084763775.2058.173.camel@lathe>
References: <1084749506.2056.148.camel@lathe> <c890e8$12ql$1@news.hub.org> <1084762532.4822.36.camel@lamb.mcmillan.net.nz> <1084763775.2058.173.camel@lathe>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox