使用SQLAlchemy merge()执行Upsert时遭遇psycopg2.UniqueViolation异常

你的问题出在**settlement这个带默认值的布尔型主键字段**上。SQLAlchemy的merge()方法执行逻辑是：先根据实例的主键字段查询数据库中是否存在匹配记录，不存在则插入，存在则更新非主键字段。但这里因为你创建entry时没有显式设置settlement的值，尽管模型定义了default=False，SQLAlchemy在生成查询条件时可能没有将settlement纳入主键匹配条件——它会认为这个主键字段是“未被显式指定”的，导致查询时只用到了其他6个显式设置的主键字段，找不到数据库中已存在的settlement=False的记录，于是尝试插入一条新记录，最终触发唯一约束冲突。

方案1：显式指定所有主键字段的值 #

在创建Offsets实例时，即使settlement有默认值，也显式把它加上：

entry=Offsets(
    trade_date='2020-09-29',
    symbol_id='AAPL',
    account_id='FOO',
    buy_qty='0',
    settlement_counterparty='BAR',
    execution_counterparty='BAZ',
    counterparty_account_id='FOOBARBAZ',
    sell_qty='1',
    daily_diff='1',
    related_orders=['some_uuids_here'],
    manual=False,
    settlement=False  # 显式指定主键字段的值
)

这样merge()会用完整的7个主键字段去查询，就能匹配到已存在的记录，执行更新而非插入。

方案2：改用SQLAlchemy的原生Upsert语法（更可靠） #

merge()的行为有时会受实例状态、字段默认值等因素影响，对于复合主键的Upsert场景，直接使用on_conflict_do_update会更直观且不易出错：

from sqlalchemy.dialects.postgresql import insert

# 构造插入语句，指定冲突时更新非主键字段
stmt = insert(Offsets).values(
    trade_date='2020-09-29',
    symbol_id='AAPL',
    account_id='FOO',
    buy_qty='0',
    settlement_counterparty='BAR',
    execution_counterparty='BAZ',
    counterparty_account_id='FOOBARBAZ',
    sell_qty='1',
    daily_diff='1',
    related_orders=['some_uuids_here'],
    manual=False,
    settlement=False
).on_conflict_do_update(
    index_elements=['symbol_id', 'account_id', 'settlement_counterparty', 'execution_counterparty', 'trade_date', 'manual', 'settlement'],  # 指定复合主键
    set_={
        'buy_qty': insert.excluded.buy_qty,
        'sell_qty': insert.excluded.sell_qty,
        'daily_diff': insert.excluded.daily_diff,
        'related_orders': insert.excluded.related_orders,
        # 其他需要更新的非主键字段
    }
)

db.session.execute(stmt)
db.session.commit()

这种方式直接通过SQL层面的Upsert逻辑处理，避开了merge()的ORM状态管理陷阱，适合复杂主键场景。

你提到移除settlement字段后merge()正常，这也佐证了问题根源：当所有主键字段都被显式设置时，merge()能正确生成完整的主键查询条件，找到已存在的记录并执行更新。而带默认值的主键字段如果未显式指定，就会导致merge()的查询条件不完整，最终触发插入冲突。

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