12

I know that \d table_name lists all columns for that table, but is there a way to list them sorted in alphabetical order?

2 Answers 2

29

Generally, use the information_schema:

SELECT column_name
FROM information_schema.columns 
WHERE table_schema = 'public' 
AND table_name = 'blah' 
ORDER BY column_name ASC;
2
  • Works perfect. Great answer!
    – oxfist
    Jan 21, 2014 at 18:52
  • 1
    If you realize you actually wanted them in the order they appear in the table,ORDER BY ordinal_position gets you pretty close (it's the order they were in at table creation).
    – Noumenon
    Apr 4, 2019 at 18:25
0

This also works:

SELECT col_attr.attname as "ColumnName" 
FROM pg_catalog.pg_attribute col_attr 
WHERE col_attr.attnum > 0 
AND NOT col_attr.attisdropped 
AND col_attr.attrelid = 
(SELECT cls.oid 
FROM pg_catalog.pg_class cls 
LEFT JOIN pg_catalog.pg_namespace ns 
ON ns.oid = cls.relnamespace 
WHERE cls.relname = 'MyTable') 
ORDER BY "ColumnName" ASC
3
  • Could you explain that query?, seems a little confusing to me.
    – oxfist
    Jan 21, 2014 at 14:58
  • @Oxfist you can find the column level description of these tables at postgresql.org/docs/9.2/static/catalogs.html
    – dezso
    Jan 21, 2014 at 16:41
  • Will do. Thank you. The previous answer was clearer, though.
    – oxfist
    Jan 21, 2014 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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