PostgreSQL：无需正则表达式截断父表并删除子表的查询方法

要精准识别符合要求的父表，我们可以借助PostgreSQL系统表pg_tables，通过表名的前缀关联关系来判断（无需正则表达式），核心逻辑如下：

• 父表本身不是任何其他表的子表（即不存在某个表的名称加下划线后与当前表名完全匹配）
• 父表存在对应的带日期后缀的子表（即存在表以当前表名加下划线开头）

父表查询语句 #

SELECT t1.tablename
FROM pg_tables t1
WHERE t1.tableowner = 'user'
  AND t1.schemaname = 'metrics'
  AND t1.tablename != 'alembic_version'
  -- 排除本身属于子表的条目
  AND NOT EXISTS (
    SELECT 1
    FROM pg_tables t2
    WHERE t2.schemaname = t1.schemaname
      AND t2.tableowner = t1.tableowner
      AND t1.tablename LIKE t2.tablename || '_%'
  )
  -- 仅保留存在对应子表的父表
  AND EXISTS (
    SELECT 1
    FROM pg_tables t3
    WHERE t3.schemaname = t1.schemaname
      AND t3.tableowner = t1.tableowner
      AND t3.tablename LIKE t1.tablename || '_%'
  );

批量生成操作语句 #

得到父表列表后，可直接生成对应的TRUNCATE和DROP执行语句，简化操作流程：

-- 生成TRUNCATE父表的执行语句
SELECT 'TRUNCATE TABLE metrics.' || tablename || ';' AS truncate_stmt
FROM pg_tables t1
WHERE t1.tableowner = 'user'
  AND t1.schemaname = 'metrics'
  AND t1.tablename != 'alembic_version'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_tables t2
    WHERE t2.schemaname = t1.schemaname
      AND t2.tableowner = t1.tableowner
      AND t1.tablename LIKE t2.tablename || '_%'
  )
  AND EXISTS (
    SELECT 1
    FROM pg_tables t3
    WHERE t3.schemaname = t1.schemaname
      AND t3.tableowner = t1.tableowner
      AND t3.tablename LIKE t1.tablename || '_%'
  );

-- 生成DROP子表的执行语句
SELECT 'DROP TABLE metrics.' || t3.tablename || ';' AS drop_stmt
FROM pg_tables t1
JOIN pg_tables t3 ON t3.schemaname = t1.schemaname
                  AND t3.tableowner = t1.tableowner
                  AND t3.tablename LIKE t1.tablename || '_%'
WHERE t1.tableowner = 'user'
  AND t1.schemaname = 'metrics'
  AND t1.tablename != 'alembic_version'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_tables t2
    WHERE t2.schemaname = t1.schemaname
      AND t2.tableowner = t1.tableowner
      AND t1.tablename LIKE t2.tablename || '_%'
  );

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