PostgreSQL查询指定表关联序列结果不全如何解决

原查询仅覆盖了序列通过OWNED BY直接绑定表字段的关联场景，遗漏了三类常见的序列关联逻辑，导致结果不全：

• 未关联schema做过滤，当不同schema下存在同名表时，依赖关系匹配会出现错乱、漏匹配
• 未覆盖「仅通过字段默认值nextval()引用、未设置OWNED BY」的序列：这类序列的依赖关系挂载在字段默认值对象（pg_attrdef）上，不会直接出现在序列与表字段的直连依赖记录中
• 未兼容PostgreSQL 10+版本的IDENTITY列、分区/继承表场景：IDENTITY列的序列依赖路径和传统serial列有差异，分区子表使用的序列通常绑定在父表上，原join条件无法命中这部分记录

替换查询中table_name为实际表名即可，如果存在跨schema同名表，放开schema过滤条件指定对应schema即可：

SELECT DISTINCT s.relname AS sec
FROM pg_class t
JOIN pg_namespace n ON n.oid = t.relnamespace
-- 匹配直接OWNED BY到字段的序列
LEFT JOIN pg_depend d_direct ON d_direct.refobjid = t.oid
  AND d_direct.classid = 'pg_class'::regclass
  AND d_direct.refclassid = 'pg_class'::regclass
LEFT JOIN pg_class s_direct ON s_direct.oid = d_direct.objid AND s_direct.relkind = 'S'
-- 匹配默认值中引用的序列
LEFT JOIN pg_attribute a ON a.attrelid = t.oid AND NOT a.attisdropped
LEFT JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
LEFT JOIN pg_depend d_def ON d_def.objid = ad.oid
LEFT JOIN pg_class s_def ON s_def.oid = d_def.refobjid AND s_def.relkind = 'S'
-- 合并两类序列结果
JOIN pg_class s ON s.oid = COALESCE(s_direct.oid, s_def.oid)
WHERE t.relname = 'table_name'
  -- 存在跨schema同名表时放开下方注释，指定schema
  -- AND n.nspname = 'target_schema_name'
  AND s.relispartition = false;

• 生产环境使用时建议必须加上schema过滤条件，避免同名表导致查询结果错误
• 查询结果加了DISTINCT去重，避免同个序列被多条依赖记录重复返回
• 过滤了relispartition = true的分区子序列，避免返回分区表自动生成的、业务不直接使用的子序列

内容的提问来源于stack exchange，提问作者Emmanuel