psycopg2导入无ID JSON触发id非空约束报错的动态ID实现方案

• serial类型的id列只有在插入语句未显式给id赋值、或显式传入DEFAULT时，才会自动调用关联序列生成自增ID。
• 你当前用json_populate_recordset(NULL::item_free, %s)生成记录时，因为JSON中无id字段，输出的记录id值固定为NULL；后续INSERT INTO item_free SELECT *的写法等价于显式给id列传入NULL，直接触发非空约束，不会触发列的默认值逻辑。

方案1（推荐）：显式指定插入字段，排除id列 #

不需要修改原始JSON，也不需要在Python层生成ID，直接调整插入SQL，只传入JSON中存在的业务字段，id完全交由数据库自增生成，修改后的代码片段如下：

import json
import psycopg2

with psycopg2.connect(host='0.0.0.0', port='0000' ,dbname='Test', user='TEST', password='TEST') as conn:
    with conn.cursor() as cur:
        with open('../json/file/Location/file.json') as my_file:
            data = json.load(my_file)
        # 仅指定业务字段，id由数据库自动生成
        query_sql = """ 
            INSERT INTO item_free (item_1, item_2, item_3, item_4, item_5, item_6, item_7, item_8, item_9, item_10)
            SELECT item_1, item_2, item_3, item_4, item_5, item_6, item_7, item_8, item_9, item_10
            FROM json_populate_recordset(NULL::item_free, %s) 
        """
        cur.execute(query_sql, (json.dumps(data),))
    conn.commit()

补充：你原代码没有显式调用commit，psycopg2默认不会自动提交DML操作，记得加上提交逻辑，否则插入不会实际生效。

方案2：查询时将id字段替换为DEFAULT #

如果后续表字段可能扩展，不想每次手动加字段名，也可以在SELECT子句中将id值显式设为DEFAULT，强制触发序列生成，示例SQL如下：

INSERT INTO item_free
SELECT 
    DEFAULT AS id,
    item_1, item_2, item_3, item_4, item_5,
    item_6, item_7, item_8, item_9, item_10
FROM json_populate_recordset(NULL::item_free, %s)

注意：该写法依然不能用SELECT *，否则还是会读取到生成的NULL id值。

你提供的JSON示例中"item_5": "Milk, 缺失闭合双引号，属于非法JSON格式，执行导入前需要先修正JSON语法，否则json.load阶段会直接抛出解析错误。

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