How to get column attributes query from table name using PostgreSQL?

Question

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!

Chris Farmiloe · Accepted Answer · 2013-04-10T16:18:00.133

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

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
Thkns a lot for your time ! :D whit this Chris Farmiloe and aswered ! — Carlos Aviles, Apr 10 '13 at 16:47

score 5 · Answer 2 · edited Jun 20 '20 at 09:12

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 no 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 on 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 that catalog tables 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.

@user2266294: Note the added disclaimer about compatibility. — Erwin Brandstetter, Apr 10 '13 at 16:58

score 0 · Answer 3 · answered Mar 02 '20 at 02:13

I think you can use this:

select ordinal_position AS num, column_name as name, data_type as typ, character_maximum_length as lenth, 
is_nullable as nullable, column_default as default
from INFORMATION_SCHEMA.COLUMNS
WHERE table_catalog='DatabaseName' AND table_name='TableName'

How to get column attributes query from table name using PostgreSQL?

3 Answers3

Compatibility

Linked