如何基于SQLAlchemy实现PostgreSQL数组列的Upsert功能？

完全可行，核心是利用PostgreSQL的数组函数结合SQLAlchemy的ON CONFLICT DO UPDATE语法，实现数组元素"存在即保留、不存在则添加"的Upsert逻辑。

实现步骤 #

1. 模型定义
   首先定义包含字符串数组列的SQLAlchemy模型：

from sqlalchemy import Column, Integer, ARRAY, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    tags = Column(ARRAY(String))  # 字符串数组类型列

2. Upsert语句实现
   借助PostgreSQL内置的array_distinct函数，在冲突更新时拼接原数组与新元素并自动去重：

from sqlalchemy import insert, func
from sqlalchemy.engine import create_engine

engine = create_engine('postgresql://user:password@host/dbname')

# 示例：Upsert id=1的记录，尝试添加元素'b'
stmt = insert(MyTable).values(id=1, tags=['b'])
stmt = stmt.on_conflict_do_update(
    index_elements=[MyTable.id],  # 冲突判断依据（主键或唯一索引）
    set_={
        # 拼接原数组与新数组后去重，确保元素不重复
        'tags': func.array_distinct(MyTable.tags || stmt.excluded.tags)
    }
)

# 执行Upsert操作
with engine.begin() as conn:
    conn.execute(stmt)

逻辑说明 #

• MyTable.tags || stmt.excluded.tags：将原数组与待插入的新数组拼接（例如原数组为{'a','b'}，新数组为{'b'}，拼接后得到{'a','b','b'}）
• array_distinct(...)：PostgreSQL内置去重函数，会返回无重复元素的数组，最终结果为{'a','b'}，完全匹配需求

单个元素的简化写法 #

如果仅需添加单个元素而非数组，可直接用func.array()包装单个值：

stmt = insert(MyTable).values(id=1, tags=func.array('b'))
# 后续Upsert逻辑与上述一致

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