今天主要簡單總結下PG資料庫平時去查看錶結構、索引、主鍵、外來鍵、觸發器的一些SQL，僅供參考。
\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