Skip site navigation (1) Skip section navigation (2)

RE: Finding column using SQL query.

From: <email(at)juergen-cappel(dot)de>
To: Rajan Bhide <rbhide(at)starentnetworks(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: RE: Finding column using SQL query.
Date: 2005-02-09 11:56:01
Message-ID: 6312635$11079498714209f92fbecdd9.17658910@config3.schlund.de (view raw or flat)
Thread:
Lists: pgsql-novice
Here is what I do in my application (slightly reduced for clarity):

    select pg_class.relname,pg_type.typname,pg_attribute.attname
    from pg_class,pg_type,pg_attribute
    where pg_attribute.attrelid=pg_class.oid
    and pg_attribute.atttypid=pg_type.oid
    and pg_class.relname = 'yourtable'

Also you may look at the reference of attrelid on this page:

http://www.postgresql.org/docs/8.0/interactive/catalog-pg-attribute.html


HTH, Jürgen




Rajan Bhide <rbhide(at)starentnetworks(dot)com> schrieb am 09.02.2005,
12:43:28:
> >>pg_attribute.attrelid=pg_class.oid
> I am not able to find any attribute name 'oid' for pg_class.
> Is this new in 8.0?
> Can you construct a query to explain this?
> 
> Thanks,
> Rajan
> 
> 
> -----Original Message-----
> From: email(at)juergen-cappel(dot)de [mailto:email(at)juergen-cappel(dot)de] 
> Sent: Wednesday, February 09, 2005 5:02 PM
> To: Rajan Bhide
> Cc: pgsql-novice(at)postgresql(dot)org
> Subject: Re: RE: [NOVICE] Finding column using SQL query.
> 
> 
> 
> You have to link like this:
> 
>     pg_attribute.attrelid=pg_class.oid
> 
> and to find the datatye of an attribute:
> 
>     pg_attribute.atttypid=pg_type.oid
> 
> 
> 
> HTH, Jürgen
> 
> 
> 
> 
> Rajan Bhide  schrieb am 09.02.2005,
> 12:10:54:
> > I tried finding relation between pg_attribute and pg_class but seems 
> > there is no common key between these two table.
> > 
> > select * from pg_attribute where attrelid = (select reltype from 
> > pg_class  where relname = 'mytablename';  attrelid | attname | 
> > atttypid | attstattarget | attlen | attnum | attndims | at
> > tcacheoff | atttypmod | attbyval | attstorage | attisset | attalign |
> > attnotnull
> >  | atthasdef
> > ----------+---------+----------+---------------+--------+--------+----
> > ----------+---------+----------+---------------+--------+--------+--
> > ----+---
> > ----------+-----------+----------+------------+----------+----------+-
> > ----------+-----------+----------+------------+----------+----------+-
> > ----------+-----------+----------+------------+----------+----------+-
> > --------
> > -+-----------
> > (0 rows)
> > 
> > 
> > So this is not solving my problem.
> > Am I missing somethg or there is some other way to find out? Plz 
> > comment.
> > 
> > Thanks,
> > Rajan
> > 
> > -----Original Message-----
> > From: email(at)juergen-cappel(dot)de [mailto:email(at)juergen-cappel(dot)de]
> > Sent: Wednesday, February 09, 2005 3:38 PM
> > To: Rajan Bhide
> > Cc: pgsql-novice(at)postgresql(dot)org
> > Subject: Re: [NOVICE] Finding column using SQL query.
> > 
> > 
> > 
> > Take a look at the system catalogs:
> > 
> > http://www.postgresql.org/docs/8.0/interactive/catalogs.html
> > 
> > HTH
> > 
> > 
> > 
> > Rajan Bhide  schrieb am 09.02.2005,
> > 10:56:20:
> > > Hi,
> > > 
> > > Is there any way to find whether a particular column exists in the
> > > table or not based on the column name using sql query? I have a 
> > > requirement where I need to find whether a column exists in the table 
> > > or not using SQL query.
> > > 
> > > Thanks,
> > > Rajan
> > > 
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 9: the planner will ignore your desire to choose an index scan if
> > your
> > >       joining column's datatypes do not match

pgsql-novice by date

Next:From: Rajan BhideDate: 2005-02-09 12:10:08
Subject: Re: Finding column using SQL query.
Previous:From: Rajan BhideDate: 2005-02-09 11:43:28
Subject: Re: Finding column using SQL query.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group