PostgreSQL查询表名称、表结构、主键（多个）

1、通过命令行查询

\d 数据库 -- 得到所有表的名字
\d 表名  -- 得到表结构

 

2、SQL方式查看表结构

　　查看表 dm.dm_fact_debit 

SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = 'dm_fact_debit'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;

 

3、既能查看表结构，也能查看多个主键

SELECT
   A.ordinal_position,
   A.column_name,
   CASE A.is_nullable WHEN 'NO' THEN 0 ELSE 1 END AS is_nullable,
   A.data_type,
   coalesce(A.character_maximum_length, A.numeric_precision, -1) as length,
   A.numeric_scale,
        CASE WHEN length(B.attname) > 0 THEN 1 ELSE 0 END AS is_pk
FROM
   information_schema.columns A
LEFT JOIN (
    SELECT
        pg_attribute.attname
    FROM
        pg_index,
        pg_class,
        pg_attribute
    WHERE
        pg_class.oid = 'dm_fact_debit' :: regclass
    AND pg_index.indrelid = pg_class.oid
    AND pg_attribute.attrelid = pg_class.oid
    AND pg_attribute.attnum = ANY (pg_index.indkey)
) B ON A.column_name = b.attname
WHERE
   A.table_schema = 'dm'
AND A.table_name = 'dm_fact_debit'
ORDER BY
   ordinal_position ASC;

 ---