PostgreSQL DESCRIBE TABLE

Question

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

User · Answer

The psql equivalent of DESCRIBE TABLE is  d table   See the psql portion of the PostgreSQL manual for more details

User · Answer

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

User · Answer

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

User · Answer

You can use this     SELECT attname  FROM pg attribute pg class  WHERE attrelid pg class oid  AND relname  TableName   AND attstattarget  lt  gt 0

User · Answer

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  quot the relation doesn not exist  quot

User · Answer

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     lt name of table gt     It s supported by many db engines

User · Answer

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

User · Answer

Try this  in the psql command-line tool     d  tablename   See the manual for more info

User · Answer

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

User · Answer

This should be the solution   SELECT   FROM information schema columns WHERE table schema    your schema     AND table name      your table

User · Answer

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  gt  0  ORDER BY a attnum

User · Answer

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

User · Answer

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

User · Answer

The best way to describe a table such as a column  type  modifiers of columns  etc    d  tablename or  d tablename

User · Answer

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  gt   0 AND t typname  lt  gt   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   gt  gt  16   amp  65535      END AS numeric precision         CASE           WHEN t typname    numeric  THEN   f atttypmod - 4  amp  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  gt  0  ORDER BY f attnum

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There are lots of ways to describe the table in PostgreSQL  The simple answer is       gt   d  lt table name gt  -- OR       gt   d   lt table name gt    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  gt  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  gt  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

User · Answer

In addition to the command line  d   lt table name gt  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

User · Answer

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

[postgresql] PostgreSQL "DESCRIBE TABLE"

Examples related to postgresql

Examples related to psql

Examples related to table-structure