psql - PostgreSQL "DESCRIBE TABLE"

ID : 456

viewed : 145

Tags : postgresqlpsqltable-structurepostgresql

Top 5 Answer for psql - PostgreSQL "DESCRIBE TABLE"

vote vote


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

\d+ tablename 

See the manual for more info.

vote vote


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.

vote vote


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.

vote vote


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 


vote vote


The psql equivalent of DESCRIBE TABLE is \d table.

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

Top 3 video Explaining psql - PostgreSQL "DESCRIBE TABLE"