current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 7 down vote favorite
6

I have a project and I need a query to get all attributes of the columns (Column Name, Position, Data Type, Not Null? and Comments) all this using table name.

I achieved get Column Name, Position Data Type and Not Null? with this query:

SELECT column_name, data_type, ordinal_position, is_nullable 
FROM information_schema."columns"
WHERE "table_name"='TABLE-NAME'

But, I need the Comments!

share|improve this question

2 Answers 2

up vote 8 down vote accepted

Here's query against the system catalog that should fetch everything you need (with a bonus primary-key field thrown in for free).

SELECT DISTINCT
    a.attnum as num,
    a.attname as name,
    format_type(a.atttypid, a.atttypmod) as typ,
    a.attnotnull as notnull, 
    com.description as comment,
    coalesce(i.indisprimary,false) as primary_key,
    def.adsrc as default
FROM pg_attribute a 
JOIN pg_class pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_index i ON 
    (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
LEFT JOIN pg_description com on 
    (pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef def ON 
    (a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
AND pg_table_is_visible(pgc.oid)
AND NOT a.attisdropped
AND pgc.relname = 'TABLE_NAME'  -- Your table name here
ORDER BY a.attnum;

Which would return results like:

 num |    name     |             typ             | notnull |       comment       | primary_key 
-----+-------------+-----------------------------+---------+---------------------+-------------
   1 | id          | integer                     | t       | a primary key thing | t
   2 | ref         | text                        | f       |                     | f
   3 | created     | timestamp without time zone | t       |                     | f
   4 | modified    | timestamp without time zone | t       |                     | f
   5 | name        | text                        | t       |                     | f
  • num: The column number
  • name: The column name
  • typ: the data type
  • notnull: Is the column defined as NOT NULL
  • comment: Any COMMENT defined for the column
  • primary_key: Is the column defined as PRIMARY KEY
  • default: The command used for the default value
share|improve this answer
    
Woooww ! Your Awesome ! Thnks a lot dud ! –  Carlos Aviles Apr 10 '13 at 15:08
    
np :) .... Mark the question as answered if it solved your issue to indicate to others that they don't need to respond. –  Chris Farmiloe Apr 10 '13 at 15:13
    
Excuse me ! if i need Sequence and Default attribute ? how can a do that ? in the same query ......sorry i dont want abuse you ! –  Carlos Aviles Apr 10 '13 at 16:07
    
You'd need to join to the pg_attrdef table... example updated –  Chris Farmiloe Apr 10 '13 at 16:18
    
+1 This is a good answer. But it has some blind spots. I added another answer. –  Erwin Brandstetter Apr 10 '13 at 16:36
up vote 2 down vote

Built on the answer by @Chris:

SELECT a.attnum
      ,a.attname                            AS name
      ,format_type(a.atttypid, a.atttypmod) AS typ
      ,a.attnotnull                         AS notnull
      ,coalesce(p.indisprimary, FALSE)      AS primary_key
      ,f.adsrc                              AS default_val
      ,d.description                        AS col_comment
FROM   pg_attribute    a 
LEFT   JOIN pg_index   p ON p.indrelid = a.attrelid AND a.attnum = ANY(p.indkey)
LEFT   JOIN pg_description d ON d.objoid  = a.attrelid AND d.objsubid = a.attnum
LEFT   JOIN pg_attrdef f ON f.adrelid = a.attrelid  AND f.adnum = a.attnum
WHERE  a.attnum > 0
AND    NOT a.attisdropped
AND    a.attrelid = 'schema.tbl'::regclass  -- table may be schema-qualified
ORDER  BY a.attnum;

But:

Table names are not unique in a database and hence also not in the system catalog. You may have to schema-qualify the name.
Use a.attrelid = 'tbl'::regclass as condition. This way you can pass myschema.mytbl as name and disambiguate. Then there is need to join to pg_class at all in this case.
Also, visibility is checked automatically for regclass and there is no need for pg_table_is_visible().

A primary key can span multiple columns. I take care of this by joining to pg_index with a.attnum = ANY(p.indkey).
indkey is of type int2vecor, which is a special case of int2[], only used in the catalogs.

I find psql -E helpful for this class of problems.

Compatibility

A specialized query like this might break after a major version update. Postgres does not guarantee the catalog tables to remain stable. It is extremely unlikely that basic elements change, but the more complex and specialized your query gets, the bigger the chance. You could use the information schema instead, which is standardized, but also comparatively slow.

share|improve this answer
    
thnks a lot dude ! :D your awesome ! –  Carlos Aviles Apr 10 '13 at 16:51
    
@user2266294: Note the added disclaimer about compatibility. –  Erwin Brandstetter Apr 10 '13 at 16:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.