如何使用psql命令在PostgreSQL中执行Oracle的DESCRIBE TABLE?


当前回答

您可以使用星号执行\d*搜索模式*,以查找与您感兴趣的搜索模式匹配的表。

其他回答

除了PostgreSQL方式(\d“something”或\dt“table”或\ds“sequence”等)

SQL标准方式,如下所示:

select column_name, data_type, character_maximum_length, column_default, is_nullable
from INFORMATION_SCHEMA.COLUMNS where table_name = '<name of table>';

它受到许多db引擎的支持。

您可以使用psql斜杠命令执行此操作:

 \d myTable describe table

它也适用于其他对象:

 \d myView describe view
 \d myIndex describe index
 \d mySequence describe sequence

来源:faqs.org

当您的表不是默认模式的一部分时,您应该写:

\d+ schema_name.table_name

否则,您将得到错误消息,即“关系不存在”

使用以下SQL语句

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'tbl_name' 
AND COLUMN_NAME = 'col_name'

若替换tbl_name和col_name,它将显示您要查找的特定列的数据类型。

在MySQL中,DESCRIBE table_name


在PostgreSQL中,\d table_name


或者,可以使用以下长命令:

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