PostgreSQL `on_conflict_do_update` parameters cannot be rendered inline

[loic-simon]

Describe the bug

The compile option "literal_binds": True is not taken into account when compiling PostgreSQL-specific Insert.on_conflict_do_update.set_ parameter.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

https://docs.sqlalchemy.org/en/21/faq/sqlexpressions.html#rendering-bound-parameters-inline

SQLAlchemy Version in Use

2.0.46

DBAPI (i.e. the database driver)

N/A

Database Vendor and Major Version

N/A

Python Version

3.13.2

Operating system

OSX

To Reproduce

from sqlalchemy import Column, Integer, MetaData, Table
from sqlalchemy.dialects.postgresql import dialect, insert

t = Table("test", MetaData(), Column("id", Integer, primary_key=True))

stmt = insert(t).values({"id": 1}).on_conflict_do_update(constraint=t.primary_key, set_={"id": 2})

print(stmt.compile(dialect=dialect(), compile_kwargs={"literal_binds": True}))

Error

Output:

INSERT INTO test (id) VALUES (1) ON CONFLICT (id) DO UPDATE SET id = %(param_1)s

Expected output:

INSERT INTO test (id) VALUES (1) ON CONFLICT (id) DO UPDATE SET id = 2

Additional context

• Reproduced in 1.4.54, same output
• Reproduced in 2.0b1, with a slightly different output (additional cast at the end: INSERT INTO test (id) VALUES (1) ON CONFLICT (id) DO UPDATE SET id = %(param_1)s::INTEGER)

I suppose the issue is more broad that this single parameter, I did not dig further for now.

Thanks!

[CaselIT]

Hi,

Thanks for reporting. Seems like a simple enough bug, se forget to pass kw to process here

sqlalchemy/lib/sqlalchemy/dialects/postgresql/base.py

Line 2419 in 18283d1

value_text = self.process(value.self_group(), use_schema=False)

and in other places in that method (and maybe others)?

Do you want to try providing a PR?

[loic-simon]

Do you want to try providing a PR?

Sure, looking into it, thanks for the pointer!

[CaselIT]

I don't think there are specific tests with "literal_binds": True.

I don't think they would be required for the PR, but if you want to add them feel free

[sqla-tester]

Loïc Simon has proposed a fix for this issue in the main branch:

PostgreSQL / Insert.on_conflict_do_update: respect compile_kwargs https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6589

[sqla-tester]

Loïc Simon has proposed a fix for this issue in the rel_2_0 branch:

PostgreSQL / Insert.on_conflict_do_update: respect compile_kwargs https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6590

[zzzeek]

we observe sqlite has the identical bug (since sqlite's impl was copied from PG's)

going to add that to the gerrit

from sqlalchemy import Column, Integer, MetaData, Table
#from sqlalchemy.dialects.postgresql import dialect, insert
from sqlalchemy.dialects.sqlite import dialect, insert


t = Table("test", MetaData(), Column("id", Integer, primary_key=True))

#stmt = insert(t).values({"id": 1}).on_conflict_do_update(constraint=t.primary_key, set_={"id": 2})
stmt = insert(t).values({"id": 1}).on_conflict_do_update(set_={"id": 2})

print(stmt.compile(dialect=dialect(), compile_kwargs={"literal_binds": True}))