Re: System catalog and identifying

On 1 Aug 2002, (9902468) wrote:

> Hi all! Didn't find any info on this, so if I missed it please guide
> me to the info thx.
>
> I know the name of the table and now I need to know what columns this
> table has.
>
> First I search system catalog named pg_class inorder to get the
> postgre id of the table named relfilenode:
>
> select relfilenode from pg_class where relname = 'table_name';
>
> after that I search all column names from pg_attribute, like this:
>
> select attname from pg_attribute where attrelid = relfilenode;
>
> unfortunately, this returns correct names, but also some names that
> the system uses. How can I exclude these names?
>
> EXAMPLE:
> I get the following results:
>
> tableoid *
> cmax *
> xmax *
> cmin *
> xmin *
> oid *
> ctid *
> sarake_id
> nimi
> kuvaus
> tyyppi
> help
> taulu_id
>
> everything marked with * is unwanted.
>

SELECT a.attname
FROM pg_attribute a, pg_class c
WHERE c.oid = a.attrelid
AND c.relname = 'table_name'
AND a.attnum > 0
ORDER BY a.attnum

let's you do it from the table name in one query and gives you them in order.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants