CODEWITHSUNDEEP
sqldatabasepostgresqlmetadata
Alfred Zhong
Alfred Zhong

Reputation: 7081

How to list only attribute names of a table in PostgreSQL

I know I can use \d to list the schema. However, I need to make a front end application just to display attribute names of a table. How can I get the only attribute names out in PostgreSQL?

Thanks!

Upvotes: 4

Views: 9060

Answers (1)

mys
mys

Reputation: 2473

You need to query appropriate tables under pg_catalog or information_schema schemas. Run psql -E ... and then psql displays meta-data queries used in internal commands like \d, \dt, \dv, ...

information_schema is same as pg_catalog but more portalbe (it is defined in SQL standard). If your app uses postgres only then I would use pg_catalog instead of information_schema

For example, this query shows columns for attribute

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'attribute'::regclass AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

UPDATE: You can use simple query like this:

SELECT attname 
FROM pg_catalog.pg_attribute 
WHERE attrelid = 'attribute'::regclass AND attnum > 0 AND NOT attisdropped

or use equivalent query:

SELECT column_name 
FROM information_schema.columns 
WHERE table_name = 'attribute' 
ORDER BY ordinal_position

If you have same table with the same name in multiple schemas you need to user schema name as well and query get slightly more complex.

Upvotes: 6

Related Questions