Thread: How do I get column names?

A simple question. <p>What is the best way to query the DB, to get the column names for a particular table? <p>Thanks!
<pre>-- 
Steven Wheeler
Mid-Range UNIX Engineering
Sabre Inc.
(918) 292-4119</pre>  

"Steven M. Wheeler" <swheeler@sabre.com> writes:
> What is the best way to query the DB, to get the column names for a
> particular table?

Usually you'd join across pg_attribute and pg_class, assuming that you
were starting from a table name.  For example:

regression=> select attname, attnum from pg_attribute, pg_class where
regression-> attrelid = pg_class.oid and relname = 'int8_tbl';
attname|attnum
-------+------
cmax   |    -6
xmax   |    -5
cmin   |    -4
xmin   |    -3
oid    |    -2
ctid   |    -1
q1     |     1
q2     |     2
(8 rows)

You probably would also want 'and attnum > 0' in the where-clause to
exclude the system attributes...
        regards, tom lane

We have an online order system that I am trying to convert to use postgres 
and I noticed a problem with the creation of the DB.

One of the tables has a field called 'oid', which is fine under lots of 
other DB's but postgres doesn't like it. I am in the process of renaming 
this field so things will work but I am curious what the error actually means:

ERROR:  create: system attribute named "oid"

Thanks,

Drew

On Tue, Dec 14, 1999 at 01:14:14PM +1300, Drew Whittle allegedly wrote:
> We have an online order system that I am trying to convert to use postgres 
> and I noticed a problem with the creation of the DB.
> 
> One of the tables has a field called 'oid', which is fine under lots of 
> other DB's but postgres doesn't like it. I am in the process of renaming 
> this field so things will work but I am curious what the error actually means:
> 
> ERROR:  create: system attribute named "oid"
> 
> Thanks,
> 
> Drew

Every record in a table has a unique OID value. So if you were to insert the
same value twice, you would be able to keep them apart using the OID value.
Since PostgreSQL uses the oid field internally, you cannot use it.

I hope this helps.

Mathijs

On Mon, 13 Dec 1999, Steven M. Wheeler wrote:

> A simple question.
> 
> What is the best way to query the DB, to get the column names for a
> particular table?
SELECT attname FROM pg_attribute, pg_class WHERE pg_class.oid = attrleid   AND attnum>0 AND relname =
'particular_table';
In this way, in each tuple of the result can find a column name.
Or can use the simple:
SELECT * FROM particular_table LIMIT 1;
This query will be return with a single line, and you can get the column
names from the result. (PQfname).

The first way may a bit faster, but the second can work with user typed
querys too.
 Best Regards
--nek;(