Query for Listing of Columns in a Table in Postgres 9

Here's my code for getting a list of a table's columns' name and data type.

One important trick here is skipping columns which have been dropped. The definition of a dropped column remains, so the query needs to filter that out with "attisdropped = False".

SELECT attname::varchar as "Column Name", pg_type.typname::varchar as "Data Type", pg_attribute.attnum as "Position in Table"
FROM pg_attribute, pg_class, pg_type
WHERE attrelid = pg_class.oid
AND pg_attribute.attisdropped = False
AND relname = 'YourTableNameGoesHere'
AND attnum > 0
AND atttypid = pg_type.oid

That code took a lot of googling and trial and error. Hope it helps save you same time and trouble.

Update as of 2011-04-29

Here's another approach I found here:

SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'YourSchemaNameGoesHere' -- Default is usually 'public'.
AND table_name = 'YourTableNameGoesHere'
ORDER BY ordinal_position;

"information_schema" is a set of meta-data defined by the SQL standard. So the code at the top of this post is Postgres-specific while the last block of code should work in any database complying with the standard.

By the way, if you see gaps in the sequence of the ordinal position numbers, that means columns were deleted from your table. Example: 1, 2, 3, 5, 6 means the 4th added column was deleted from this table's definition.