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

Re: Retrieving column names and table names of a database

From: nourry(at)ensinfo(dot)univ-nantes(dot)fr (Frederic Nourry)
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Retrieving column names and table names of a database
Date: 1999-05-18 12:52:41
Message-ID: 199905181252.OAA03376@dec1.ensinfo.univ-nantes.fr (view raw or flat)
Thread:
Lists: pgsql-sql
>Hi,
>, this may sound stupid, but is there a way of retrieving the table names and >column names of a database using SQL. I know there
>is \d in psql, but I donĀ“t know if there is something similar in SQL or if there >is a function I can call. 
>Please reply even if there is no function
>
>Thanks in advance for your help
>
>Matthias
>. 

 To retrieve the table names , use the pg_tables :
    SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%';

 To retrieve the column names , you can use the pg_attribute and
pg_type tables :
     SELECT attname , typname FROM pg_attribute , pg_type WHERE   
  typrelid=attrelid AND typname = 'banals' ;
     if your table name is 'banals' , but this will give you 6 columns that
you haven't declared : cmax,xmax,cmin,xmin,oid,ctid .

  If you want column names with its type :
      SELECT attname , opcname , atttypmod FROM pg_attribute,pg_type,
    pg_opclass WHERE attrelid=typrelid AND atttypid=opcdeftype AND 
    typname = 'banals' ;
          if your table name is 'banals' , but this will give the
column 'oid' you haven't declared

  You can find all tables names in <where you put psql>//data/base/ .

 Sorry for my english !

pgsql-sql by date

Next:From: Ana RoizenDate: 1999-05-18 14:44:57
Subject: Updating and null values.
Previous:From: Hiroshi InoueDate: 1999-05-18 10:36:40
Subject: spinlock freeze again

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