I know that \d table_name lists all columns for that table, but is there a way to list them sorted in alphabetical order?
Add a comment
|
2 Answers
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, 2014 at 12:50
-
-
1If you realize you actually wanted them in the order they appear in the table,
ORDER BY ordinal_positiongets you pretty close (it's the order they were in at table creation).– NoumenonCommented Apr 4, 2019 at 18:25
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
-
Could you explain that query?, seems a little confusing to me.– oxfistCommented 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 Commented Jan 21, 2014 at 16:41
-
Will do. Thank you. The previous answer was clearer, though.– oxfistCommented Jan 21, 2014 at 18:54