如何在PostgreSQL中高效合并多客户端同结构的相似表？

方法1：用动态SQL自动生成合并查询 #

不用手动复制N次SELECT语句，借助PostgreSQL的系统目录表就能自动拼接出完整的合并查询。

生成完整的合并SQL语句 #

执行下面的SQL，会直接输出拼接好的UNION ALL查询，复制结果执行就能拿到所有客户端的数据：

SELECT string_agg(
  format(
    'select field_1, field_2, ''%I'' as client from %I.purchases',
    schema_name,
    schema_name
  ),
  ' UNION ALL '
) AS merged_query
FROM information_schema.schemata
WHERE schema_name LIKE 'client%'; -- 按你的模式命名规则调整过滤条件，比如匹配client_开头的模式

直接返回合并结果（可选） #

如果不想每次都复制生成的SQL，可以写个PL/pgSQL函数，调用后直接返回数据：

CREATE OR REPLACE FUNCTION get_all_purchases()
RETURNS TABLE(field_1 TEXT, field_2 TEXT, client TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE (
    SELECT string_agg(
      format(
        'select field_1, field_2, ''%I'' as client from %I.purchases',
        schema_name,
        schema_name
      ),
      ' UNION ALL '
    )
    FROM information_schema.schemata
    WHERE schema_name LIKE 'client%'
  );
END;
$$ LANGUAGE plpgsql;

之后只要执行SELECT * FROM get_all_purchases();就能直接获取合并后的所有采购数据。

方法2：创建持久化视图（适合频繁查询） #

如果需要经常查合并数据，基于上面的函数创建视图更方便：

CREATE OR REPLACE VIEW all_purchases AS
SELECT * FROM get_all_purchases();

后续查询直接用SELECT * FROM all_purchases;就行，新增客户端模式后，重新执行视图创建语句就能更新视图内容。

关键注意点 #

• 模式命名要有统一规则，比如都以client开头，这样过滤条件才能精准匹配目标模式。
• 如果有不需要包含的模式，在WHERE子句里排除，比如AND schema_name NOT IN ('client_test', 'client_archive')。
• 用%I做标识符转义，能避免特殊字符导致的SQL语法错误，同时防止SQL注入风险。

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