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


当前回答

这个问题的变体(如其他答案中所解释的)对我很有用。

SELECT
 COLUMN_NAME
FROM
 information_schema.COLUMNS
WHERE
 TABLE_NAME = 'city';

详细描述如下:http://www.postgresqltutorial.com/postgresql-describe-table/

其他回答

当表名以大写字母开头时,应将表名放在引号中。

示例:\d“用户”

即使请求了psql命令,我也会添加pg_dump命令。因为它生成了以前的MySQl用户更常见的输出。

#sudo-u postgres pg_dump--table=my_table_name--仅模式mydb

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

您可以使用此选项:

SELECT attname 
FROM pg_attribute,pg_class 
WHERE attrelid=pg_class.oid 
AND relname='TableName' 
AND attstattarget <>0; 

在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