Hi all, I'm almost sure I'm missing the obvious, struggling to understand how to use a CTE with a PG "upsert" statement: I need to modify an existing (and working as expected) update statement based on a CTE to an upsert, because now the target information is kept in a different table.
As said, while my current CTE+UPDATE works, I could not figure out what I'm doing wrong with the new "upsert": invariably I obtain a statement where the CTE part is missing, and thus a "TableNotFound" exception where the INSERT ... ON CONFLICT ... references the CTE relation. After initial trials and failures, I distilled the following snippet, where even a plain INSERT does not come out as expected: the first statement, an UPDATE based on the CTE, is complete, while the latter two, a plain INSERT based on the CTE and the "upsert" variant, do not: from sqlalchemy import Table, Column, String, Integer, MetaData, create_engine, select from sqlalchemy.dialects.postgresql import insert metadata = MetaData() e = create_engine("postgresql://scott:tiger@localhost/test") orders = Table('orders', metadata, Column('region', String, primary_key=True), Column('amount', Integer)) some_sales = (select([orders.c.region, orders.c.amount]) .where(orders.c.region.in_(['a', 'b'])) .cte("some_sales")) stmt = (orders.update() .values(amount=some_sales.c.amount*2) .where(orders.c.region == some_sales.c.region)) print(stmt.compile(e)) print('='*10) stmt = (orders.insert() .values(region=some_sales.c.region, amount=some_sales.c.amount*2)) print(stmt.compile(e)) print('='*10) istmt = (insert(orders) .values(region=some_sales.c.region, amount=some_sales.c.amount*2)) upsert = istmt.on_conflict_do_update( index_elements=[orders.c.region], set_=dict(amount=some_sales.c.amount*2)) print(upsert.compile(e)) print('='*10) This emits: WITH some_sales AS (SELECT orders.region AS region, orders.amount AS amount FROM orders WHERE orders.region IN (%(region_1)s, %(region_2)s)) UPDATE orders SET amount=(some_sales.amount * %(amount_1)s) FROM some_sales WHERE orders.region = some_sales.region ========== INSERT INTO orders (region, amount) VALUES (some_sales.region, (some_sales.amount * %(amount_1)s)) ========== INSERT INTO orders (region, amount) VALUES (some_sales.region, (some_sales.amount * %(amount_1)s)) ON CONFLICT (region) DO UPDATE SET amount = (some_sales.amount * %(amount_2)s) ========== Can anybody shed some light on what I'm doing wrong? Thanks in advance, ciao, lele. -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. l...@metapensiero.it | -- Fortunato Depero, 1929. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/877e4ed71x.fsf%40metapensiero.it.