向含非空ID列的PostgreSQL表插入数据时遇NotNullViolation错误

表结构 #

CREATE TABLE sales (
    id BIGINT NOT NULL,  -- Primary Key
    tahun INTEGER NOT NULL,
    bulan NUMERIC NOT NULL,
    v_kod VARCHAR(10) NOT NULL,
    o_kod VARCHAR(10) NOT NULL,
    amaun_rm NUMERIC NOT NULL,
    dt_updated TIMESTAMP WITHOUT TIME ZONE NOT NULL
);

Python代码 #

import pandas as pd
import psycopg2

# Load the CSV data
forecast_results = pd.read_csv("sales.csv")

# Filter the DataFrame to include only rows from September 2024 onward
filtered_forecast_results = forecast_results[(forecast_results['tahun'] > 2024) | 
                                             ((forecast_results['tahun'] == 2024) & (forecast_results['bulan'] >= 9))]

# Define the vot_kod value to be inserted
vot_kod_value = 'AAA'

# Connect to PostgreSQL
conn = psycopg2.connect(
    dbname="my_database",
    user="my_user",
    password="my_password",
    host="localhost",
    port="5432"
)
cur = conn.cursor()

for index, row in filtered_forecast_results.iterrows():
    # Convert the year and month to integers, but keep o_kod as a string
    tahun = int(row['tahun'])
    bulan = int(row['bulan'])
    o_kod = str(row['o_kod '])
    
    # Check if the row already exists
    cur.execute("""
        SELECT 1 FROM sales 
        WHERE tahun = %s AND bulan = %s AND v_kod = %s AND o_kod = %s
    """, (tahun, bulan, v_kod_value, o_kod))
    
    exists = cur.fetchone()
    
    if not exists:
        # If the row does not exist, insert it
        sql_query = """
            INSERT INTO sales (tahun, bulan, v_kod, o_kod, amaun_rm, dt_updated)
            VALUES (%s, %s, %s, %s, %s, NOW())
        """
        values = (
            tahun,
            bulan,
            v_kod_value,
            o_kod,
            round(row['predicted_amaun_rm'], 2)
        )
        cur.execute(sql_query, values)

# Commit the transaction
conn.commit()

# Close the cursor and connection
cur.close()
conn.close()

错误信息 #

NotNullViolation: null value in column "id" of relation "sales" violates not-null constraint
DETAIL:  Failing row contains (null, 2024, 9, AAA, 123, 2931.48, 2024-08-16 08:39:52.462847).

已尝试操作 #

• 排除id列：尝试在INSERT语句中排除id列，以为PostgreSQL会自动生成它，但导致NotNullViolation错误。
• 手动生成ID：未手动指定id，因为认为它应该自动递增。

问题列表 #

1. 如何正确插入数据并确保id列被正确填充？
2. 是否应该将id列设置为PostgreSQL中的自动递增列？如果是，如何修改表实现这一点？
3. 是否可以在Python代码中获取并使用下一个可用的ID值再进行插入？

解答 #

1. 正确插入数据的方法 #

错误核心是id列仅标记为NOT NULL，但未配置自动生成规则，PostgreSQL无法自动填充该值。解决方式有两种：要么修改表结构让id自动递增（推荐），要么插入时手动提供合法的id值。

2. 应该设置id为自动递增列，修改方法如下 #

PostgreSQL推荐使用IDENTITY列（SQL标准语法）实现自动递增，比旧的SERIAL更规范。执行以下SQL修改表：

-- 先将id设为主键（若未设置）
ALTER TABLE sales ADD PRIMARY KEY (id);

-- 配置id为自动递增列
ALTER TABLE sales ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

如果是新建表，直接将id列定义改为：

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL,

修改完成后，原INSERT语句无需改动，PostgreSQL会自动为新插入行生成唯一id。

3. 可以手动获取下一个ID，但不推荐 #

通过查询当前最大id加1获取下一个ID：

cur.execute("SELECT COALESCE(MAX(id), 0) + 1 FROM sales")
next_id = cur.fetchone()[0]

然后在INSERT语句中加入id参数：

INSERT INTO sales (id, tahun, bulan, v_kod, o_kod, amaun_rm, dt_updated)
VALUES (%s, %s, %s, %s, %s, %s, NOW())

并将next_id加入values元组。

但这种方法存在并发风险：多进程同时执行时可能获取相同ID，导致主键冲突。因此强烈建议使用数据库内置的自动递增机制，而非手动生成ID。

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