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

Question

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

Solution 1

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)
        );

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

Solution 2

SELECT
    column_name,
    data_type
FROM
    information_schema.columns
WHERE
    table_name = 'table_name';

with the above query you can columns and its datatype

Solution 3

Open psql command line and type :

\d+ table_name

Solution 4

Don't forget to add the schema name in case you have multiple schemas with the same table names.

SELECT column_name, data_type 
FROM information_schema.columns
WHERE table_name = 'your_table_name' AND table_schema = 'your_schema_name';

or using psql:

\d+ your_schema_name.your_table_name

Solution 5

A version that supports finding the column names and types of a table in a specific schema, and uses JOINs without any subqueries

SELECT
    pg_attribute.attname AS column_name,
    pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
FROM
    pg_catalog.pg_attribute
INNER JOIN
    pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
    pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
    pg_attribute.attnum > 0
    AND NOT pg_attribute.attisdropped
    AND pg_namespace.nspname = 'my_schema'
    AND pg_class.relname = 'my_table'
ORDER BY
    attnum ASC;

View more solutions

Comments

[Lukasz Szozda]

plsql \gdesc is also an option. The neat feature is it could describe entire queries(useful for complex adhoc ones) but it works for simplified scenario SELECT * FROM tab_name \gdesc as well.

[GT.]

That won't give the right answer for user-defined types (e.g., Geometry and Geography columns created by ogr2ogr, which are of the form geometry(Geometry,[SRID])).

[Marco Mannes]

One might also use table_catalog = 'my_database' and table_schema = 'my_schema' in order to get only columns from a specific table of a specific schema of a specific database.

[mjezzi]

I don't understand why this isn't the most upvoted answer.

[Luke]

This is incomplete as OP may want to do this programmatically in SQL code, not just via psql.

[Keyur Padalia]

Works, but why are you using c.relname ~ '^(hello world)$ instead of simply c.relname = 'hello world'?

[karatedog]

Postgres does it programmatically, so just start postgres with the '-E' flag: psql -E and for every backslash command the respective SQL will be displayed before the result of the command.

[Donald Duck]

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value.

[Daniel L. VanDenBosch]

May I suggest to everyone, if you are looking to build on this code. Use the pg_catalog and not the information_schema. The information_schema has some pretty easy and universal SQL, however it is slower because it is higher level.

[Daniel L. VanDenBosch]

This is my favorite answer because it accomplishes 2 things. It uses the pg_catalog and it forces you specify the schema. If you are cramming everything thing in the public schema, I personally believe that is a bad strategy. As your project grows, it will be difficult to keep things organized. IMHO

[Daniel L. VanDenBosch]

Please include context with your code. Also, how do you expand this to other schemas?

[Alexander Trauzzi]

Presuming OP has access to psql makes this answer a little out of scope. Having postgres does not presume proficiency or the ability to access psql.

[nurettin]

I was initially dismissive of this answer, but surprisingly, this worked out the best for me when creating a dblink. I just copied and pasted the output into a spreadsheet, split by | got the first two columns, added a comma column and a ); at the end and we have a dblink query.