Originally posted by nesati February 3, 2026
I think I found a bug, but the bug reporting page sent me here. Here is an example:
from sqlalchemy import BigInteger, Integer, String, create_engine
from sqlalchemy.dialects.postgresql import insert as postgres_upsert
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
user_id: Mapped[int] = mapped_column(BigInteger(), primary_key=True)
gov_id: Mapped[int] = mapped_column(Integer(), nullable=False, unique=True)
first_name: Mapped[str] = mapped_column(String(50), nullable=False)
last_name: Mapped[str] = mapped_column(String(50), nullable=False)
if __name__ == "__main__":
engine = create_engine("postgresql+psycopg2://localhost")
with Session(engine) as session:
Base.metadata.create_all(session.connection())
stmt = postgres_upsert(User)
stmt = stmt.on_conflict_do_update(
index_elements=["gov_id"],
set_={
"first_name": stmt.excluded.first_name,
"last_name": stmt.excluded.last_name,
},
)
list(
session.scalars(
stmt.returning(User, sort_by_parameter_order=True),
[
{
"gov_id": 1,
"first_name": "Jane",
"last_name": "Doe",
},
{
"gov_id": 2,
"first_name": "John",
"last_name": "Smith",
},
],
)
)This results in
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidColumnReference) table "imp_sen" has 3 columns available but 4 columns specified
SQL produced by SQLAlchemy seems invalid:
INSERT INTO users (gov_id, first_name, last_name)
SELECT p0::INTEGER, p1::VARCHAR, p2::VARCHAR
FROM (
VALUES (%(gov_id)s, %(first_name)s, %(last_name)s)
) AS imp_sen(p0, p1, p2, sen_counter)
ORDER BY sen_counter
ON CONFLICT (gov_id)
DO UPDATE SET first_name = excluded.first_name, last_name = excluded.last_name
RETURNING users.user_id, users.gov_id, users.first_name, users.last_name, users.user_id AS user_id__1
Python version: 3.13.2
SQLAlchemy version: 2.0.46
PostgreSQL version: 17.6
psycopg2 version: 2.9.11
This issue seems to occur only when all the conditions are met:
- there is
.on_conflict_do_update
- there is
.returning(sort_by_parameter_order=True)
- there are multiple rows in the insert
Expected behavior: I'd expect SQLAlchemy to insert or update the given rows and return them in order without raising exception or for this to raise an exception that clearly states that this is not a supported use case.
Discussed in #13106
Originally posted by nesati February 3, 2026
I think I found a bug, but the bug reporting page sent me here. Here is an example:
This results in
SQL produced by SQLAlchemy seems invalid:
Python version: 3.13.2
SQLAlchemy version: 2.0.46
PostgreSQL version: 17.6
psycopg2 version: 2.9.11
This issue seems to occur only when all the conditions are met:
.on_conflict_do_update.returning(sort_by_parameter_order=True)Expected behavior: I'd expect SQLAlchemy to insert or update the given rows and return them in order without raising exception or for this to raise an exception that clearly states that this is not a supported use case.