sqlpostgresqlsqldatatypes

How to get a list column names and datatypes of a table in PostgreSQL?


How can I get a list of column names and datatypes of a table in PostgreSQL using a query?


Solution

  • SELECT
            a.attname as "Column",
            pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
        FROM
            pg_catalog.pg_attribute a
        WHERE
            a.attnum > 0
            AND NOT a.attisdropped
            AND a.attrelid = (
                SELECT c.oid
                FROM pg_catalog.pg_class c
                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                WHERE c.relname ~ '^(hello world)$'
                    AND pg_catalog.pg_table_is_visible(c.oid)
            );
    

    Change the hello world with your table name

    More info on it : http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html