pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew McMillan <andrew@catalyst.net.nz>
To: Scot L. Harris <webid@cfl.rr.com>
Cc: pgsql-php@postgresql.org
Subject: Re: table column information
Date: Mon, 17 May 2004 22:26:22 +1200
Message-ID: <1084789582.27913.7.camel@lamb.mcmillan.net.nz> (raw)
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>

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




view thread (11+ messages)  latest in thread

Message-ID: <1084789582.27913.7.camel@lamb.mcmillan.net.nz>
Permalink:  ../1084789582.27913.7.camel@lamb.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1084789582.27913.7.camel@lamb.mcmillan.net.nz

 · 

reply

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: andrew@catalyst.net.nz, webid@cfl.rr.com
  Subject: Re: table column information
  In-Reply-To: <1084789582.27913.7.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> <1084763775.2058.173.camel@lathe> <1084789582.27913.7.camel@lamb.mcmillan.net.nz>

* 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