PostgreSQL中含同名列的表连接与批量列名重命名方案

当你用USING(col_a)连接表A和B时，SELECT *会导致重复列名（如id、col_b），无法直接创建视图。针对你不想手动给50+列逐个加别名的需求，以下是几种高效解决方案：

方法1：动态SQL自动生成带前缀的查询 #

这是最适配你场景的方案，通过查询系统元数据自动生成表B列的别名，无需手动维护：

DO $$
DECLARE
    b_cols text;
BEGIN
    -- 提取表B中除连接列col_a外的所有列，生成带b_前缀的别名
    SELECT string_agg(quote_ident(column_name) || ' AS b_' || quote_ident(column_name), ', ')
    INTO b_cols
    FROM information_schema.columns
    WHERE table_name = 'b'
      AND column_name != 'col_a';

    -- 执行创建视图的动态SQL
    EXECUTE format('
        CREATE VIEW my_view AS
        SELECT a.*, %s
        FROM A a
        JOIN B b USING(col_a)
    ', b_cols);
END $$;

• 自动处理表B的所有列，列结构变动时重新执行即可更新视图
• 执行前可将EXECUTE替换为RAISE NOTICE '%',，先检查生成的SQL是否符合预期

方法2：借助jsonb实现批量列转换 #

通过将表B的行转为jsonb，展开后添加前缀再转回列（适合需要灵活处理列的场景）：

CREATE VIEW my_view AS
SELECT
    a.*,
    -- 提取表B除col_a外的列并添加前缀
    (jsonb_to_record(to_jsonb(b) - 'col_a') AS x(b_id int, b_col_b int, b_col_e int)).*
FROM A a
JOIN B b USING(col_a);

• 需要在jsonb_to_record中定义转换后的列名和类型，适合列结构相对稳定的情况
• 相比动态SQL，需要手动写出一次转换后的列结构，但后续使用时无需重复操作

方法3：批量替换列名创建视图 #

如果能接受一次性导出表结构并批量替换，可直接在视图定义中指定列：

CREATE VIEW my_view (
    id, col_a, col_b, col_c,
    b_id, b_col_b, b_col_e
) AS
SELECT a.*, b.id, b.col_b, b.col_e
FROM A a
JOIN B b USING(col_a);

• 可通过数据库工具导出表B的列名，用文本编辑器批量替换为b_原列名，快速生成SELECT子句
• 适合列结构长期稳定的场景，后续维护需要手动更新列定义

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