PostgreSQL "DESCRIBE TABLE"

Question

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

Noisome

Answer 1

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

\d+ tablename

See the manual for more info.

Viticulture

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. – Noisome

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 – Bovill

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

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

Note: This only works from the command-line psql client. Under the hood, it issues a query to get the information from the server as shown in the answer below. – Mattress

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

Whoever came up with a CLI interface that makes one letter commands that start with the same letter as drop or delete should not be designing databases. – Fishbolt

This is a hard one to remember, keep confuding \dt (list tables in a database) with \d+ tablename (describe a table). – Sangfroid

I always come here, and never remember the sytax :D... this is the 3rd time this month :D – Reo

Try below one too. It gives nice output. https://mcmap.net/q/44964/-postgresql-quot-describe-table-quot – Xeniaxeno

Answer 2

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.

Tizes

select column_name,data_type,character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'table'; – Castellan

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. – Nunhood

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

Wonderful, altought for postgres I'd add the schema name too – Posit

\d, \d+ do not work from Navicat. This little query is good enough! This should have been the answer! – Intrigue

I would add ORDER BY ordinal_position to make it even better. – Berger

update to @NewAlexandria : \d+ works in redshift now – Evangelineevangelism

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. – Docia

@Docia Did you try SELECT *? – Description

@Description The 'columns' table doesn't hold info about things like primary key, foreign keys, indexes, check constraints. In one project I left join from 'columns' to 'key_column_usage' and 'table_constraints' to pick up PK and FK constraints. All the info is in that schema for sure. – Docia

This is actually the most precise answer, as Oracle's DESCRIBE will list only the column information and so does this command. All the other stuff reported by \d (indexes, constraints) are not part of a simple DESCRIBE, and often I don't want to see them - pity there's no \d- command to make psql be a little less verbose... – Chesterchesterfield

Works in PgAdmin 4 Query Tool – Maryannmaryanna

Answer 3

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 pg_get_expr(d.adbin, d.adrelid)
    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.

Sudbury

This query is better shown here note that they suggest "\d table" too – Shamblin

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. – Grangerize

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

Fails in v12, because pg_attrdef.adsrc does not exist anymore. – Minx

@Minx I updated it for v12, just replacing d.adsrc with pg_get_expr(d.adbin, d.adrelid) works fine – Elegize

Answer 4

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

Graben

Answer 5

This should be the solution:

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

Roxannroxanna

This is a more complete answer. If you are not getting any rows back, try this. One must always provide schema name when in doubt. – Maemaeander

Answer 6

The psql equivalent of DESCRIBE TABLE is \d table.

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

Noisome

Also, psql database selction is \c databasename rather than use databasename (for those coming from MySQL like myself :-). Without \c databasename first, \d tablename produces No relations found. message and nothing more. – Overarch

Well, you can just \d databasename.tablename too. With proper quoting if necessary, i.e. \d "DatabaseName"."TableName", if your names are not all lowercase. – Zippel

Answer 7

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

Tabernacle

This was what I was looking for - how to describe a subset of tables. Of note, I also found that if your tables have uppercase, the syntax is \d *"<SubString>"*. That is, the double quotes must be inside the asterisks. Though, if you just want the list of tables then you want to use \dt – Credulous

this matches sequences and indexes as well as tables – Leavy

Answer 8

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'

Lander

FROM info_schema.columns didn't work for me I had to use from information_schema.columns, not sure if that's a typo in your answer or some implementation issue at my end. – Forcefeed

Answer 9

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.

Hindgut

That's what this answer from 2008 says. – Adornment

@Quentin-There is difference in both of them..the above 2008 Solution describes column_name, data_type, character_maximum_length for the whole table. Where as mine - the mentioned solution - only shows the data type of the schema column. Run both and check. They both are different. All the solutions here are different ways to solve a problem. User can use this for different reasons – Hindgut

Answer 10

You can use this :

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

Henleigh

Answer 11

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

Clinometer

Answer 12

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/

Hillell

Answer 13

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

Debility

Answer 14

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
;

Sudatorium

Answer 15

When your table name starts with a capital letter you should put your table name in the quotation.

Example: \d "Users"

Guanidine

One of those weird idiosyncrasies of databases... – Joletta

Answer 16

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.

*Using PG admin3

Supertanker

because selecting and expecting the pgadmin to pickup the slack of getting the meta data is not "best practice" – Falstaffian

The LIMIT clause is evaluated after both FROM and SELECT; therefore, this query would take a long time to finish if the table in question is large. – Collateral

Answer 17

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

\d+ tablename or \d tablename

Ashwell

Answer 18

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                        |

Selfinductance

Answer 19

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."

Kibler

Answer 20

\dt can describe multiple tables simply:

\dt

\dt can describe a single table simply:

\dt <table>

\d can describe multiple tables in detail:

\d <table> <table>

\d+ can describe multiple tables in more detail:

\d+ <table> <table>

In addition, pg_tables can describe a table simply:

SELECT * FROM pg_tables WHERE tablename = '<table>';

And, information_schema.columns can describe a tables in much more detail:

SELECT * FROM information_schema.columns WHERE table_name = '<table>';

Knead

None of these worked for me. – Boykins

@Boykins You should restore a backup because your database is corrupted. You can see => #74984779 – Knead

Answer 21

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/

Heterogamy

Answer 22

To get description use,

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

To get the indexes use,

SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'table_name' ORDER BY indexname;

Xeniaxeno

Answer 23

/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

Retreat

Answer 24

I'll add the pg_dump command even thou the psql command was requested. because it generate an output more common to previous MySQl users.

# sudo -u postgres pg_dump --table=my_table_name --schema-only mydb

Thrust

Answer 25

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;

Decorum

|| appears to be something like a concatenation operator (joining strings together) – Leavy