Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: SQL Syn : How to get the list of table's column with it's data type

    hi...
    1st of all, i want to apologize for my broken english...

    i'm here about to ask if anybody here knows how to get table's column name with it's data type,
    i try to join the pg_tables and pg_type but the data type of that column wasn't appear
    if i type "select * from pg_tables where tablename not like 'pg_%';"

    the result is like below :-
    schemaname | tablename | tableowner | hasindexes | hasrules | hastriggers
    -------------------------------------------------------------------------
    public | table1 | owner1 | f | f | f
    public | table2 | owner2 | f | f | f


    how can i get the result like this :-
    tablename | datatype | defaultvalues
    --------------------------------------------------------------------------
    table1 |varchar(12) | abc
    table1 |varchar(12) | def
    table1 |varchar(12) | ghi
    table1 |varchar(12) | jkl
    table1 |varchar(12) | mno
    table2 |int | 123
    table2 |int | 345
    table2 |int | 567
    table2 |int | 789
    table2 |int | 980


    is it can be done by joining the "pg_" file..
    if the answer is yes, how...?
    can anyone tell me plss...

  2. #2
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    >is it can be done by joining the "pg_" file..
    >if the answer is yes, how...?
    Yes, its possible. See Chapter 43. System Catalogs
    Or if you is lazy ;-)
    select c.relname, a.attname, t.typname||'('||a.attlen||')', d.adsrc
    from
    pg_class c , pg_attribute a, pg_type t, pg_attrdef d
    where (c.relkind='r' or c.relkind='v') --for tables and views
    AND a.attrelid = c.oid
    AND t.oid=a.atttypid
    AND d.adrelid=c.oid
    AND d.adnum=a.attnum

  3. #3
    Join Date
    Jan 2005
    Posts
    5
    tq...
    it works...
    now i know how to use the "pg_" tables...
    thanx again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •