[postgresql] PostgreSQL "DESCRIBE TABLE"

There are lots of ways to describe the table in PostgreSQL

The simple answer is

    > /d <table_name> -- OR

    > /d+ <table_name>

Usage

If you are in Postgres shell [psql] and you need to describe the tables

You can achieve this by Query also [As lots of friends has posted the correct ways]

There are lots of details regarding the Schema are available in Postgres's default table names information_schema. You can directly use it to retrieve the information of any of table using a simple SQL statement.

Easy query

    SELECT
      *
    FROM
      information_schema.columns
    WHERE
      table_schema = 'your_schema' AND
      table_name   = 'your_table';

Medium query

  SELECT
      a.attname AS Field,
      t.typname || '(' || a.atttypmod || ')' AS Type,
      CASE WHEN a.attnotnull = 't' THEN 'YES' ELSE 'NO' END AS Null,
      CASE WHEN r.contype = 'p' THEN 'PRI' ELSE '' END AS Key,
      (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid), '\'(.*)\'')
              FROM
                      pg_catalog.pg_attrdef d
              WHERE
                      d.adrelid = a.attrelid
                      AND d.adnum = a.attnum
                      AND a.atthasdef) AS Default,
      '' as Extras
  FROM
        pg_class c 
        JOIN pg_attribute a ON a.attrelid = c.oid
        JOIN pg_type t ON a.atttypid = t.oid
        LEFT JOIN pg_catalog.pg_constraint r ON c.oid = r.conrelid 
                AND r.conname = a.attname
  WHERE
        c.relname = 'tablename'
        AND a.attnum > 0

  ORDER BY a.attnum

You just need to replace the tablename.

Hard query

  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 = 'schema'  -- Replace with Schema name  
      AND c.relname = 'tablename'  -- Replace with table name  
      AND f.attnum > 0 ORDER BY number;

You can choose any of the above ways, to describe the table.

Any of you can edit these answers to improve the ways. I'm open to merge your changes. :)