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;
---
posted on 2020-07-15 00:44 wenbin_ouyang 阅读(8392) 评论(0) 编辑 收藏 举报