从含主键的JSON文件导入PostgreSQL，避免主键重复报错咨询

问题根源 #

通过COPY或手动插入包含主键id的JSON数据后，PostgreSQL的自增序列（由autoIncrement字段自动创建）的当前值并未同步更新到表中最大id值。当后续插入新数据时，序列会生成已存在的id，触发duplicate key value violates unique constraint错误。

正确解决方案 #

1. 手动更新单表的主键序列 #

首先确认目标表的序列名称（Sequelize为autoIncrement字段创建的序列格式通常为"表名_id_seq"，注意大小写和引号），然后将序列值设置为表中最大id值：

方法一：自动获取序列名（推荐）

-- 更新Users表的序列
SELECT setval(pg_get_serial_sequence('"Users"', 'id'), (SELECT MAX(id) FROM "Users"));

-- 更新Comments表的序列
SELECT setval(pg_get_serial_sequence('"Comments"', 'id'), (SELECT MAX(id) FROM "Comments"));

方法二：直接指定序列名

如果已知序列名称（可通过SELECT pg_get_serial_sequence('"Users"', 'id');查询），可以直接执行：

SELECT setval('"Users_id_seq"', (SELECT MAX(id) FROM "Users"));
SELECT setval('"Comments_id_seq"', (SELECT MAX(id) FROM "Comments"));

如果表中无数据（MAX(id)返回null），可以将序列初始化为1：

SELECT setval('"Users_id_seq"', 1, false);

2. 批量更新所有表的主键序列 #

如果有多个需要处理的表，可以用PostgreSQL的匿名函数批量处理：

DO $$
DECLARE
    rec record;
BEGIN
    -- 遍历所有带自增序列的字段
    FOR rec IN SELECT tablename, columnname 
               FROM information_schema.columns 
               WHERE column_default LIKE 'nextval(%'
    LOOP
        -- 动态执行更新序列的SQL
        EXECUTE format(
            'SELECT setval(pg_get_serial_sequence(%L, %L), COALESCE((SELECT MAX(%I) FROM %I), 1))',
            rec.tablename, rec.columnname, rec.columnname, rec.tablename
        );
    END LOOP;
END $$;

3. 在Sequelize中执行更新 #

将上述SQL通过Sequelize的query方法执行即可：

// 单个表更新
await sequelize.query(`SELECT setval(pg_get_serial_sequence('"Users"', 'id'), (SELECT MAX(id) FROM "Users"));`);
await sequelize.query(`SELECT setval(pg_get_serial_sequence('"Comments"', 'id'), (SELECT MAX(id) FROM "Comments"));`);

// 批量更新
await sequelize.query(`
DO $$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT tablename, columnname 
               FROM information_schema.columns 
               WHERE column_default LIKE 'nextval(%'
    LOOP
        EXECUTE format(
            'SELECT setval(pg_get_serial_sequence(%L, %L), COALESCE((SELECT MAX(%I) FROM %I), 1))',
            rec.tablename, rec.columnname, rec.columnname, rec.tablename
        );
    END LOOP;
END $$;
`);

避免后续错误的注意事项 #

• 插入新数据时不要手动指定id，让Sequelize/PostgreSQL通过自增序列自动生成主键：

  // 正确写法：不指定id
  Users.create({ name: 'ofo' });
  

• 导入数据后务必执行序列更新操作，确保序列当前值与表中最大id同步。

你之前尝试的错误原因 #

1. make_serial函数不存在：这不是PostgreSQL的内置函数，因此报错。
2. 创建新序列失败：Sequelize已经为autoIncrement字段创建了对应的序列，无需重复创建；且你使用的序列名user_id_seq与实际生成的"Users_id_seq"（大小写敏感）不匹配，导致找不到序列。

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