PostgreSQL 12 -列出与其相关表名不匹配的序列

基础概念

PostgreSQL 是一个功能强大的开源关系型数据库管理系统（RDBMS）。在 PostgreSQL 中，序列（Sequence）是一种特殊的数据库对象，用于生成唯一的数值。序列通常与表中的主键或唯一标识列关联。

相关优势

1. 唯一性保证：序列可以确保生成的数值是唯一的，这在主键或唯一标识列中非常有用。
2. 自动递增：序列可以自动递增，简化了插入新记录时的操作。
3. 灵活性：序列可以独立于表存在，便于管理和维护。

类型

PostgreSQL 中的序列主要有以下几种类型：

1. 标准序列：最常见的序列类型，按固定步长递增。
2. 循环序列：当达到最大值后，会从头开始循环。
3. 扩展序列：允许自定义更多的序列参数。

应用场景

序列常用于以下场景：

1. 主键生成：为表中的主键列生成唯一标识。
2. 唯一标识生成：为表中的唯一标识列生成唯一值。
3. 时间戳生成：为记录生成唯一的时间戳。

问题：列出与其相关表名不匹配的序列

在 PostgreSQL 中，有时会出现序列与表名不匹配的情况，这可能是由于以下原因：

1. 手动创建序列：有时序列是手动创建的，但没有正确关联到表。
2. 迁移或复制问题：在数据库迁移或复制过程中，序列和表的关联可能会丢失。
3. 删除表后未删除序列：删除表后，相关的序列没有被删除，导致序列与表名不匹配。

解决方法

可以通过以下 SQL 查询列出与其相关表名不匹配的序列：

SELECT
    s.relname AS sequence_name,
    c.relname AS table_name,
    a.attname AS column_name
FROM
    pg_class s
JOIN
    pg_depend d ON s.oid = d.objid
JOIN
    pg_class c ON d.refobjid = c.oid
JOIN
    pg_attribute a ON c.oid = a.attrelid AND a.attnum = d.refobjsubid
WHERE
    s.relkind = 'S'
    AND c.relkind = 'r'
    AND NOT EXISTS (
        SELECT 1
        FROM pg_class t
        JOIN pg_attribute ta ON t.oid = ta.attrelid AND ta.attnum = 1
        WHERE t.oid = c.oid AND ta.attname = 'id'
    );

解释

1. pg_class：存储数据库对象（如表和序列）的信息。
2. pg_depend：存储对象之间的依赖关系。
3. pg_attribute：存储表列的信息。
4. 查询条件：
   • s.relkind = 'S'：筛选出序列对象。
   • c.relkind = 'r'：筛选出表对象。
   • NOT EXISTS：检查表中是否存在名为 id 的列，如果不存在，则认为序列与表名不匹配。

参考链接

PostgreSQL 官方文档 - 序列

通过以上方法，可以有效地列出与其相关表名不匹配的序列，并根据具体情况进行相应的处理。