2011-04-20

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.

3 comments:

  1. Thanks so much for publishing your results! This was such a time saver.

    It might be nice to add the atttypmod attribute to return the maximum lengths of VARCHAR fields.

    SELECT attname::varchar as "column_name",
    pg_type.typname::varchar as "data_type",
    pg_attribute.atttypmod as "length",
    pg_attribute.attnum as "position"
    FROM pg_attribute, pg_class, pg_type
    WHERE attrelid = pg_class.oid
    AND pg_attribute.attisdropped = False
    AND relname = 'your_table_name'
    AND attnum > 0
    AND atttypid = pg_type.oid;

    Hope this helps someone else...

    Jeff

    ReplyDelete
  2. I find that information_schema tables are extremely slow on busy systems, but at the same time querying the pg_attribute table directly is instant

    ReplyDelete
  3. You save me a lot of time, thank you !!

    ReplyDelete