SyntaxFix

[postgresql] PostgreSQL "DESCRIBE TABLE"

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

This question is related to postgresql psql table-structure

The answer is

/dt is the commad which lists you all the tables present in a database. using
/d command and /d+ we can get the details of a table. The sysntax will be like
* /d table_name (or) \d+ table_name

Use the following SQL statement 

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'tbl_name' 
AND COLUMN_NAME = 'col_name'

If you replace tbl_name and col_name, it displays data type of the particular coloumn that you looking for.

In MySQL , DESCRIBE table_name

In PostgreSQL , \d table_name

Or , you can use this long command:

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 can use this : 

SELECT attname 
FROM pg_attribute,pg_class 
WHERE attrelid=pg_class.oid 
AND relname='TableName' 
AND attstattarget <>0;

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. :)

The best way to describe a table such as a column, type, modifiers of columns, etc.

\d+ tablename or \d tablename

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.

To improve on the other answer's SQL query (which is great!), here is a revised query. It also includes constraint names, inheritance information, and a data types broken into it's constituent parts (type, length, precision, scale). It also filters out columns that have been dropped (which still exist in the database).

SELECT
    n.nspname as schema,
    c.relname as table,
    f.attname as column,  
    f.attnum as column_id,  
    f.attnotnull as not_null,
    f.attislocal not_inherited,
    f.attinhcount inheritance_count,
    pg_catalog.format_type(f.atttypid,f.atttypmod) AS data_type_full,
    t.typname AS data_type_name,
    CASE  
        WHEN f.atttypmod >= 0 AND t.typname <> 'numeric'THEN (f.atttypmod - 4) --first 4 bytes are for storing actual length of data
    END AS data_type_length, 
    CASE  
        WHEN t.typname = 'numeric' THEN (((f.atttypmod - 4) >> 16) & 65535)
    END AS numeric_precision,   
    CASE  
        WHEN t.typname = 'numeric' THEN ((f.atttypmod - 4)& 65535 )
    END AS numeric_scale,       
    CASE  
        WHEN p.contype = 'p' THEN 't'  
        ELSE 'f'  
    END AS is_primary_key,  
    CASE
        WHEN p.contype = 'p' THEN p.conname
    END AS primary_key_name,
    CASE  
        WHEN p.contype = 'u' THEN 't'  
        ELSE 'f'
    END AS is_unique_key,
    CASE
        WHEN p.contype = 'u' THEN p.conname
    END AS unique_key_name,
    CASE
        WHEN p.contype = 'f' THEN 't'
        ELSE 'f'
    END AS is_foreign_key,
    CASE
        WHEN p.contype = 'f' THEN p.conname
    END AS foreignkey_name,
    CASE
        WHEN p.contype = 'f' THEN p.confkey
    END AS foreign_key_columnid,
    CASE
        WHEN p.contype = 'f' THEN g.relname
    END AS foreign_key_table,
    CASE
        WHEN p.contype = 'f' THEN p.conkey
    END AS foreign_key_local_column_id,
    CASE
        WHEN f.atthasdef = 't' THEN d.adsrc
    END AS default_value
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 f.attisdropped = false
    AND n.nspname = '%s'  -- Replace with Schema name  
    AND c.relname = '%s'  -- Replace with table name  
    AND f.attnum > 0 
ORDER BY f.attnum
;

When your table is not part of the default schema, you should write:

\d+ schema_name.table_name

Otherwise, you would get the error saying that "the relation doesn not exist."

Use this command 

\d table name

like 

\d queuerecords

             Table "public.queuerecords"
  Column   |            Type             | Modifiers
-----------+-----------------------------+-----------
 id        | uuid                        | not null
 endtime   | timestamp without time zone |
 payload   | text                        |
 queueid   | text                        |
 starttime | timestamp without time zone |
 status    | text                        |

This variation of the query (as explained in other answers) worked for me.

SELECT
 COLUMN_NAME
FROM
 information_schema.COLUMNS
WHERE
 TABLE_NAME = 'city';

It's described here in details: http://www.postgresqltutorial.com/postgresql-describe-table/

This should be the solution:

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

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.

You may do a \d *search pattern * with asterisks to find tables that match the search pattern you're interested in.

1) PostgreSQL DESCRIBE TABLE using psql

In psql command line tool, \d table_name or \d+ table_name to find the information on columns of a table

2) PostgreSQL DESCRIBE TABLE using information_schema

SELECT statement to query the column_names,datatype,character maximum length of the columns table in the information_schema database;

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name = 'tablename';

For more information https://www.postgresqltutorial.com/postgresql-describe-table/

In addition to the command line \d+ <table_name> you already found, you could also use the information-schema to look up the column data, using info_schema.columns 

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

The psql equivalent of DESCRIBE TABLE is \d table.

See the psql portion of the PostgreSQL manual for more details.

In postgres \d is used to describe the table structure.

e.g. \d schema_name.table_name

this command will provide you the basic info of table such as, columns, type and modifiers.

If you want more info about table use

\d+ schema_name.table_name

this will give you extra info such as, storage, stats target and description

You can do that with a psql slash command:

 \d myTable describe table

It also works for other objects:

 \d myView describe view
 \d myIndex describe index
 \d mySequence describe sequence

Source: faqs.org

You can also check using below query

Select * from schema_name.table_name limit 0;

Expmple : My table has 2 columns name and pwd. Giving screenshot below.

Adding image

*Using PG admin3

I worked out the following script for get table schema.

'CREATE TABLE ' || 'yourschema.yourtable' || E'\n(\n' ||
array_to_string(
array_agg(
'    ' || column_expr
)
, E',\n'
) || E'\n);\n'
from
(
SELECT '    ' || column_name || ' ' || data_type || 
coalesce('(' || character_maximum_length || ')', '') || 
case when is_nullable = 'YES' then ' NULL' else ' NOT NULL' end as column_expr
FROM information_schema.columns
WHERE table_schema || '.' || table_name = 'yourschema.yourtable'
ORDER BY ordinal_position
) column_list;

Source: Stackoverflow.com

Examples related to postgresql

• Subtracting 1 day from a timestamp date • pgadmin4 : postgresql application server could not be contacted. • Psql could not connect to server: No such file or directory, 5432 error? • How to persist data in a dockerized postgres database using volumes • input file appears to be a text format dump. Please use psql • Postgres: check if array field contains value? • Add timestamp column with default NOW() for new rows only • Can't connect to Postgresql on port 5432 • How to insert current datetime in postgresql insert query • Connecting to Postgresql in a docker container from outside

Examples related to psql

• The Response content must be a string or object implementing __toString(), "boolean" given after move to psql • psql: command not found Mac • How to select a schema in postgres when using psql? • "psql: could not connect to server: Connection refused" Error when connecting to remote database • PostgreSQL: Why psql can't connect to server? • How to show data in a table by using psql command line interface? • Using psql how do I list extensions installed in a database? • psql: FATAL: database "<user>" does not exist • List tables in a PostgreSQL schema • postgresql port confusion 5433 or 5432?

Examples related to table-structure

• PostgreSQL "DESCRIBE TABLE"

Tags

Solution Helix Startup-error Ccw Watcom Mod-php Conditional-statements Mvccontrib-grid Resource-management Usb-flash-drive Concrete Cocoa Monad-transformers Codesense Parallel-extensions Title-case Language-comparisons Msde Datarepeater Publickeytoken Enyim Webtop Slide Role-base-authorization Imageshack Avcapture Minicpan Spectral Delta-index Citrix Direct3d9 Pgi Wcf-rest-starting-guide Codeblocks Area Shared-memory Scsi Interface-design Pvrtc Spotlight Decompiling Url-modification Xquery-sql Variable-length-array Displaylist Uinavigationitem Typechecking Extreme-feedback-devices Usbserial Omnicomplete Linksys Param Activexobject Gnu Delimited Python-mode Ergonomics Rtc Distance Z-machine Sql-server-agent Totem Jsch Copy-paste Webwork Subviews Gyroscope Protege User-accounts Objectcontext Iis-manager Forward-declaration Mirah Speechsynthesizer Wss-3.0 Simplehttpserver Ixmldomnode Executors Rabbitmq Shapefile App-code Antlr Fast-enumeration Headless-browser Readline Opengl-es-lighting Batch-rename Pipeline Swi-prolog Crowdsourcing Microphone Datagridviewcellstyle Clover Spring-remoting Schema.rb Boost-uuid Zend-filter Ca2000 Memcachedb Firefox3.5