PostgreSQL从关联表批量插入数据到user_to_property表并处理非自增主键问题

报错原因 #

你当前写法的核心问题是(SELECT selectedData.userId FROM selectedData)子查询会返回多条用户ID记录，无法直接填入VALUES语法要求的单值位置，因此触发多行返回错误。

前置准备 #

首先需要给user_to_property表创建user_id和property_id的联合唯一索引，保证两者组合的唯一性，为后续冲突处理提供基础：

CREATE UNIQUE INDEX idx_unique_user_property ON user_to_property(user_id, property_id);

批量插入实现方案 #

PostgreSQL 版本

WITH selectedData AS (
    -- 对user_id去重，避免同个用户重复生成记录
    SELECT DISTINCT t2.user_id as userId
    FROM property_lines t1
    INNER JOIN "user" t2 ON t1.account_id = t2.account_id
    -- 提前过滤已经存在的记录，减少无效处理
    WHERE NOT EXISTS (
        SELECT 1 FROM user_to_property utp 
        WHERE utp.user_id = t2.user_id AND utp.property_id = 3
    )
),
max_id AS (
    -- 取当前最大主键值，COALESCE处理空表场景
    SELECT COALESCE(MAX(user_to_property_id), 0) as current_max FROM user_to_property
)
INSERT INTO user_to_property (user_to_property_id, user_id, property_id, created_date)
SELECT 
    -- 用窗口函数给每条待插入记录分配连续递增的主键
    m.current_max + ROW_NUMBER() OVER(ORDER BY s.userId) as user_to_property_id,
    s.userId,
    3 as property_id,
    NOW() as created_date
FROM selectedData s, max_id m
-- 冲突兜底，重复记录直接跳过不报错
ON CONFLICT (user_id, property_id) DO NOTHING;

MySQL 版本

-- 先获取当前最大主键值
SET @current_max = (SELECT COALESCE(MAX(user_to_property_id), 0) FROM user_to_property);

INSERT IGNORE INTO user_to_property (user_to_property_id, user_id, property_id, created_date)
SELECT 
    @current_max := @current_max + 1 as user_to_property_id,
    s.userId,
    3 as property_id,
    NOW() as created_date
FROM (
    SELECT DISTINCT t2.user_id as userId
    FROM property_lines t1
    INNER JOIN `user` t2 ON t1.account_id = t2.account_id
    WHERE NOT EXISTS (
        SELECT 1 FROM user_to_property utp 
        WHERE utp.user_id = t2.user_id AND utp.property_id = 3
    )
) s;

注意事项 #

• 上述手动生成主键的方案在高并发插入场景下可能出现主键冲突，若业务存在高并发写入需求，建议将主键改为自增类型，或使用数据库原生序列（Sequence）生成主键，安全性更高。
• 所有过滤和去重逻辑已经提前处理了重复记录，最后的冲突处理为兜底逻辑，保证插入操作的幂等性。

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