当前位置 : 首页 » 互动问答 » 正文

PostgreSQL "DESCRIBE TABLE"

分类 : 互动问答 | 发布时间 : 2008-09-21 04:47:48 | 评论 : 18 | 浏览 : 897863 | 喜欢 : 1526

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

回答(18)

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

    \d+ tablename
    

    See the manual for more info.

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

    It's supported by many db engines.

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

  • 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

  • 5楼
  • The psql equivalent of DESCRIBE TABLE is \d table.

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

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

  • 7楼
  • You can use this :

    SELECT attname 
    FROM pg_attribute,pg_class 
    WHERE attrelid=pg_class.oid 
    AND relname='TableName' 
    AND attstattarget <>0; 
    
  • 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'
    
  • 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.

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

    \d+ tablename or \d tablename
    
  • 12楼
  • 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
    
  • 13楼
  • 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

  • 14楼
  • 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                        |
    
  • 15楼
  • /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

  • 16楼
  • 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
    
  • 17楼
  • 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
    ;
    
  • 18楼
  • 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;
    

相关阅读:

Check if value exists in Postgres array

Show tables in PostgreSQL

PostgreSQL "DESCRIBE TABLE"

PostgreSQL "DESCRIBE TABLE"

PostgreSQL "DESCRIBE TABLE"

我如何(或可以)在多列上选择DISTINCT?

How to start PostgreSQL server on Mac OS X?

Select first row in each GROUP BY group?

How to select the nth row in a SQL database table?

如何将CSV文件数据导入PostgreSQL表?