概述

今天主要简单总结下PG数据库平时去查看表结构、索引、主键、外键、触发器的一些SQL，仅供参考。

一、查看XX表结构

 \d tablename  

或者如下：

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 = 'pg_stat_database' 
AND A.attnum > 0 
AND A.attrelid = C.oid
AND A.atttypid = T.oid
ORDER BY
A.attnum;

二、查看表索引

SELECT A
.SCHEMANAME,
A.TABLENAME,
A.INDEXNAME,
A.TABLESPACE,
A.INDEXDEF,
B.AMNAME,
C.INDEXRELID,
C.INDNATTS,
C.INDISUNIQUE,
C.INDISPRIMARY,
C.INDISCLUSTERED,
D.DESCRIPTION
FROM
PG_AM B
LEFT JOIN PG_CLASS F ON B.OID = F.RELAM
LEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELID
LEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELID
LEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID,
PG_INDEXES A
WHERE
A.SCHEMANAME = E.SCHEMANAME
AND A.TABLENAME = E.RELNAME
AND A.INDEXNAME = E.INDEXRELNAME
AND E.SCHEMANAME = 'public' --and E.RELNAME = 't_student';
--或者直接查系统视图
select * from pg_indexes

三、查看表主键

--查询主键名称
select c.relname,p.conname from pg_constraint p inner join pg_class c on p.conrelid=c.oid where p.contype='p'
and c.relname='t_bdt_budget_d'
--查询主键的详细信息
SELECT C.relname,
P.conname,
A.attname,
T.typname
FROM
pg_constraint
P INNER JOIN pg_class C ON P.conrelid = C.oid
INNER JOIN pg_attribute A ON A.attrelid = C.oid
AND A.attnum = P.conkey [ 1 ]
INNER JOIN pg_type T ON T.oid = A.atttypid
WHERE
P.contype = 'p' 
AND C.relname = 't_bdt_budget_d'

四、查看表外键

-- 查看当前表ID
SELECT oid, relname FROM pg_class WHERE relname = 'syslogfilter';
-- 查看引用当前表ID作参考表的主外键约束信息
SELECT * FROM pg_CONSTRAINT WHERE confrelid = '24935';
-- 查看那些外键的名称
SELECT oid, relname FROM pg_class WHERE oid in
(
SELECT conrelid FROM pg_CONSTRAINT WHERE confrelid = '24935'
);

五、查看触发器

\dy：查看触发器

--当前数据库所有的触发器
SELECT * FROM pg_trigger
--特定表的触发器
SELECT *  FROM pg_trigger t, pg_class  c WHERE t.tgrelid=c.oid AND c.relname='company';

六、查看视图

\dv: 查看所有自己创建的视图
\dv+: 查看所有自己创建的视图，显示大小

 select * from pg_views