How to list all table columns in sorted order?

Question

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

score 25 · Accepted Answer · answered Jan 20 '14 at 12:50

25

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;

answered Jan 20 '14 at 12:50

Craig Ringer

51,279
3
136
175

Works perfect. Great answer! – oxfist Jan 21 '14 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 04 '19 at 18:25

score 0 · Answer 2 · edited Jan 20 '14 at 14:39

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

edited Jan 20 '14 at 14:39

Mark Storey-Smith

31,155
6
85
122

answered Jan 20 '14 at 13:52

Parthi P

1

Could you explain that query?, seems a little confusing to me. – oxfist Jan 21 '14 at 14:58
@Oxfist you can find the column level description of these tables at http://www.postgresql.org/docs/9.2/static/catalogs.html – dezso Jan 21 '14 at 16:41
Will do. Thank you. The previous answer was clearer, though. – oxfist Jan 21 '14 at 18:54

How to list all table columns in sorted order?

2 Answers2