GaussDB(DWS)运维 — SQL操作 — 查找所有包含主键&唯一索引的表信息

SELECT
n.nspname AS schemaname, –schema名称
c1.relname AS tablename, — 表名
c2.relname AS indexname, — 索引名称
s.conname AS conname,    — 约束名称
pg_get_constraintdef(s.oid) AS constraintdef, — 如果是约束，输出约束定义
CASE WHEN s.conname IS NULL THEN pg_get_indexdef(x.indexrelid) END AS indexdef — 如果不是约束，输出索引定义
FROM pg_index x
INNER JOIN pg_class c1 ON c1.oid = x.indrelid
INNER JOIN pg_class c2 ON c2.oid = x.indexrelid
INNER JOIN pg_namespace n ON n.oid = c1.relnamespace
LEFT JOIN pg_constraint s ON s.conrelid = x.indrelid AND s.conindid = x.indexrelid
WHERE (x.indisprimary = true OR x.indisunique = true)
AND c1.relkind = ‘r’
AND x.indrelid >= 16384 AND x.indexrelid > 16384
AND (c1.reloptions IS NULL OR c1.reloptions::text not like ‘%internal_mask%’)  — 排除内置对象
ORDER BY schemaname, tablename, indexname
;