Home PostgreSQL "DESCRIBE TABLE"
Reply: 14

PostgreSQL "DESCRIBE TABLE"

Mr. Muskrat
1#
Mr. Muskrat Published in 2008-09-20 20:47:48Z

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

IMSoP
2#
IMSoP Reply to 2015-02-26 11:55:36Z

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

\d+ tablename

See the manual for more info.

Daniel Serodio
3#
Daniel Serodio Reply to 2014-03-05 20:49:33Z

The psql equivalent of DESCRIBE TABLE is \d table.

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

wvdz
4#
wvdz Reply to 2015-09-22 19:18:33Z

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

Community
5#
Community Reply to 2017-05-23 12:10:54Z

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
from INFORMATION_SCHEMA.COLUMNS where table_name = '<name of table>';

It's supported by many db engines.

Richard Neish
6#
Richard Neish Reply to 2012-10-22 09:38:18Z

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.

Tshepang
7#
Tshepang Reply to 2014-06-05 14:07:30Z

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

Daniel L. VanDenBosch
8#
Daniel L. VanDenBosch Reply to 2017-08-28 21:10:25Z

You can use this :

SELECT attname 
FROM pg_attribute,pg_class 
WHERE attrelid=pg_class.oid 
AND relname='TableName' 
AND attstattarget <>0; 
roadrunner66
9#
roadrunner66 Reply to 2016-04-17 19:57:09Z

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'
Daniel L. VanDenBosch
10#
Daniel L. VanDenBosch Reply to 2017-08-28 17:41:24Z

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.

Tajinder
11#
Tajinder Reply to 2016-12-12 14:51:12Z

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

Usman Yaqoob
12#
Usman Yaqoob Reply to 2017-07-20 13:22:39Z
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                        |
Guardian
13#
Guardian Reply to 2017-08-06 15:25:06Z

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

\d+ tablename or \d tablename
Pavan Teja
14#
Pavan Teja Reply to 2017-08-09 06:03:09Z

/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

codeUrDream
15#
codeUrDream Reply to 2018-02-14 13:46:49Z

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/

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.322343 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO