Categories
postgresql psql table-structure

PostgreSQL “DESCRIBE TABLE”

2036

How do you perform the equivalent of Oracle’s DESCRIBE TABLE in PostgreSQL (using the psql command)?

0

    3035

    Try this (in the psql command-line tool):

    \d+ tablename
    

    See the manual for more info.

    9

    • 11

      I had originally accepted devinmoore’s answer but I really like this one better. Not only does it describe the table but it also shows the metadata such as column descriptions and if there are any OIDs.

      Sep 20, 2008 at 21:08

    • 36

      The + is really clutch, as PostgresSQL 9 only gives the in-depth description for views when you do \d+ table_name, rather than the simple \d table_name

      – nessur

      May 4, 2011 at 22:08

    • 13

      \d doesn’t work when you invoke it in PosgreSQL 9.1 through pgAdmin, Vinko’s answer below is applicable to more cases

      Jul 18, 2012 at 13:38

    • 14

      psql -E is handy to get the sql that implements \d+ and similar (for use outside of the psql prompt)

      – bsb

      Aug 19, 2013 at 5:34

    • 24

      Error: “did not find any relation named”. This means you need to wrap your table’s name in double quotes. Apparently, postgres will lower case your table name without them and therefore not find your table. Hope this helps anyone else who comes here and has this problem. 🙂

      – amurrell

      Mar 31, 2015 at 0:57

    849

    In addition to the PostgreSQL way (\d ‘something’ or \dt ‘table’ or \ds ‘sequence’ and so on)

    The SQL standard way, as shown here:

    select column_name, data_type, character_maximum_length, column_default, is_nullable
    from INFORMATION_SCHEMA.COLUMNS where table_name="<name of table>";
    

    It’s supported by many db engines.

    11

    • 25

      select column_name,data_type,character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = ‘table’;

      – SO Stinks

      Sep 23, 2010 at 3:05

    • 8

      This is more useful than \d when you’re stuck with an pre-8.4 psql and a post-8.4 server – the \d command is incompatible.

      – beldaz

      Oct 5, 2010 at 19:21

    • 31

      Also this command runs against RedShift, where \d+ does not. This is the best answer IMO

      Apr 3, 2013 at 14:27


    • 8

      Wonderful, altought for postgres I’d add the schema name too

      – ffflabs

      Oct 17, 2014 at 16:21

    • 3

      This only lists columns with minimal information. \d+ gives full DDL for the table including: defaults, nullability, nextval, precision, primary key, foreign keys, indexes, check constraints, and FK’s from other tables.

      – bradw2k

      Jan 19, 2017 at 18:08

    75

    If you want to obtain it from query instead of psql, you can query the catalog schema. Here’s a complex query that does that:

    SELECT  
        f.attnum AS number,  
        f.attname AS name,  
        f.attnum,  
        f.attnotnull AS notnull,  
        pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,  
        CASE  
            WHEN p.contype="p" THEN 't'  
            ELSE 'f'  
        END AS primarykey,  
        CASE  
            WHEN p.contype="u" THEN 't'  
            ELSE 'f'
        END AS uniquekey,
        CASE
            WHEN p.contype="f" THEN g.relname
        END AS foreignkey,
        CASE
            WHEN p.contype="f" THEN p.confkey
        END AS foreignkey_fieldnum,
        CASE
            WHEN p.contype="f" THEN g.relname
        END AS foreignkey,
        CASE
            WHEN p.contype="f" THEN p.conkey
        END AS foreignkey_connnum,
        CASE
            WHEN f.atthasdef="t" THEN d.adsrc
        END AS default
    FROM pg_attribute f  
        JOIN pg_class c ON c.oid = f.attrelid  
        JOIN pg_type t ON t.oid = f.atttypid  
        LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum  
        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace  
        LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)  
        LEFT JOIN pg_class AS g ON p.confrelid = g.oid  
    WHERE c.relkind = 'r'::char  
        AND n.nspname="%s"  -- Replace with Schema name  
        AND c.relname="%s"  -- Replace with table name  
        AND f.attnum > 0 ORDER BY number
    ;
    

    It’s pretty complex but it does show you the power and flexibility of the PostgreSQL system catalog and should get you on your way to pg_catalog mastery ;-). Be sure to change out the %s’s in the query. The first is Schema and the second is the table name.

    3

    • 3

      This query is better shown here note that they suggest “\d table” too

      Oct 12, 2011 at 14:05


    • 4

      One advantage of this solution is that format_type() will include any modifiers attached to the type, e.g. numeric(6,2); whereas information_schema.columns will only report the base type of numeric.

      Dec 6, 2014 at 2:26

    • 3

      How do I split the data type from the size? say | character varying(50) | to 2 columns: | character varying | 50 |

      – ivanceras

      May 20, 2015 at 10:44