如何在PostgreSQL的所有表中查找包含指定值的表名

下面是两种无需额外工具、直接在数据库内执行的方案，可快速定位所有包含目标值'Alida'的表：

方案1：原生动态查询（无需插件，新手推荐） #

该方案用PL/pgSQL遍历所有字符类型列逐一匹配，不需要安装任何扩展，适合中小库使用：

注意事项 #

• 大库执行会占用一定IO资源，生产环境建议在业务低峰期运行
• 默认排除系统表，仅搜索用户自建表，可自行调整schema过滤条件

执行代码 #

DO $$
DECLARE
    -- 替换下方引号内的值为你的搜索目标
    search_val TEXT := 'Alida';
    cur RECORD;
    match_cnt INT;
BEGIN
    -- 遍历所有用户表的字符类列
    FOR cur IN
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
          AND data_type IN ('text', 'character', 'character varying')
          -- 如需限定搜索指定schema，取消下方注释并替换为你的schema名
          -- AND table_schema = 'public'
    LOOP
        -- 动态查询该列是否包含目标值（默认不区分大小写模糊匹配）
        EXECUTE format(
            'SELECT COUNT(*) FROM %I.%I WHERE %I ILIKE $1',
            cur.table_schema, cur.table_name, cur.column_name
        ) INTO match_cnt USING '%' || search_val || '%';

        -- 匹配到结果就输出信息
        IF match_cnt > 0 THEN
            RAISE NOTICE '匹配成功 | Schema: %, 表名: %, 匹配列: %', cur.table_schema, cur.table_name, cur.column_name;
        END IF;
    END LOOP;
END $$;

结果查看 #

执行后从控制台的NOTICE输出中即可获取所有符合要求的表名，可根据需求调整匹配规则：

• 要精确匹配完全等于Alida的值：把ILIKE $1改为= $1，USING后的内容改为search_val
• 要区分大小写匹配：把ILIKE改为LIKE

方案2：大库加速方案（使用pg_trgm扩展） #

如果你的库数据量很大，方案1执行过慢，可以用官方扩展pg_trgm加速搜索：

• 安装扩展：CREATE EXTENSION IF NOT EXISTS pg_trgm;
• 给需要搜索的字符列创建GIN索引：CREATE INDEX idx_[表名]_[列名]_trgm ON [表名] USING GIN ([列名] gin_trgm_ops);
• 再执行方案1的查询语句，搜索速度会提升数倍到数十倍

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