PostgreSQL通用去重时，INSERT INTO能否直接使用*省略列名？

SQL本身至今仍不支持用子查询直接替换INSERT语句中的列列表，不过可以通过应用层动态拼接SQL或数据库端存储过程两种方式实现通用去重，无需提前知晓表结构。

方案一：应用层（psycopg2）动态拼接SQL #

利用psycopg2的sql模块安全获取列名并拼接SQL，避免注入风险，步骤如下：

import psycopg2
from psycopg2 import sql

def deduplicate_table(conn, table_name, unique_column):
    with conn.cursor() as cur:
        # 按原表列顺序获取所有列名
        cur.execute(sql.SQL("""
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name = %s 
            ORDER BY ordinal_position
        """), (table_name,))
        columns = [row[0] for row in cur.fetchall()]
        column_list = sql.SQL(', ').join(map(sql.Identifier, columns))

        # 定义标识符，避免SQL注入
        temp_table = sql.Identifier(f"temp_{table_name}")
        target_table = sql.Identifier(table_name)
        unique_col = sql.Identifier(unique_column)

        # 执行去重逻辑
        cur.execute(sql.SQL("CREATE TABLE {} (LIKE {})").format(temp_table, target_table))
        cur.execute(sql.SQL("""
            INSERT INTO {} ({})
            SELECT DISTINCT ON ({}) {}
            FROM {}
        """).format(temp_table, column_list, unique_col, column_list, target_table))
        cur.execute(sql.SQL("DROP TABLE {}").format(target_table))
        cur.execute(sql.SQL("ALTER TABLE {} RENAME TO {}").format(temp_table, target_table))
    conn.commit()

方案二：数据库端PL/pgSQL存储过程 #

在数据库中创建存储过程，直接在数据库层面完成动态SQL生成与执行：

CREATE OR REPLACE FUNCTION deduplicate_table(table_name text, unique_column text)
RETURNS void AS $$
DECLARE
    column_list text;
    temp_table text := 'temp_' || table_name;
BEGIN
    -- 按原表顺序拼接列名字符串
    SELECT string_agg(quote_ident(column_name), ', ')
    INTO column_list
    FROM information_schema.columns
    WHERE table_name = deduplicate_table.table_name
    ORDER BY ordinal_position;

    -- 执行去重操作
    EXECUTE format('CREATE TABLE %I (LIKE %I)', temp_table, table_name);
    EXECUTE format('
        INSERT INTO %I (%s)
        SELECT DISTINCT ON (%I) %s
        FROM %I
    ', temp_table, column_list, unique_column, column_list, table_name);
    EXECUTE format('DROP TABLE %I', table_name);
    EXECUTE format('ALTER TABLE %I RENAME TO %I', temp_table, table_name);
END;
$$ LANGUAGE plpgsql;

使用方式：

SELECT deduplicate_table('目标表名', '去重依据列名');

关键说明 #

• 两种方案都通过information_schema.columns获取列名，并按ordinal_position排序，保证列顺序与原表一致；
• 始终使用标识符转义（psycopg2的sql.Identifier、PL/pgSQL的quote_ident/format），避免SQL注入风险；
• SQL语法层面仍不支持用子查询替换INSERT的列列表，这一规则目前没有变化。

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