Skip to content

Independent CTEs #6752

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
zzzeek opened this issue Jul 12, 2021 Discussed in #6750 · 4 comments
Closed

Independent CTEs #6752

zzzeek opened this issue Jul 12, 2021 Discussed in #6750 · 4 comments
Labels
sql use case not really a feature or a bug; can be support for new DB features or user use cases not anticipated
Milestone

Comments

@zzzeek
Copy link
Member

zzzeek commented Jul 12, 2021

example:

from sqlalchemy import table, column
from sqlalchemy.dialects.postgresql import insert

t = table("t", column("c1"), column("c2"))

delete_statement_cte = t.delete().where(t.c.c1 < 1).cte("deletions")

insert_stmt = insert(t).values([{"c1": 1, "c2": 2}])
update_statement = insert_stmt.on_conflict_do_update(
    index_elements=[t.c.c1],
    set_={
        col.name: col
        for col in insert_stmt.excluded
        if col.name in ("c1", "c2")
    },
).add_cte(delete_statement_cte)

produces:

WITH deletions AS 
(DELETE FROM t WHERE t.c1 < %(c1_1)s)
 INSERT INTO t (c1, c2) VALUES (%(c1_m0)s, %(c2_m0)s) ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2
@zzzeek zzzeek added sql use case not really a feature or a bug; can be support for new DB features or user use cases not anticipated labels Jul 12, 2021
@zzzeek zzzeek added this to the 1.4.x milestone Jul 12, 2021
@zzzeek zzzeek changed the title Independent CTEs in PostgreSQL Independent CTEs Jul 12, 2021
@sqla-tester
Copy link
Collaborator

Mike Bayer has proposed a fix for this issue in the master branch:

implement independent CTEs https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2957

@Morikko
Copy link

Morikko commented Aug 4, 2021

Just 2 questions on the feature:

  1. The fix does not include compound select (like union). Is it on purpose ?
  2. If someone wants to do it on a ORM query, it should be done like this query.statement.add_cte() ?

@zzzeek
Copy link
Member Author

zzzeek commented Aug 4, 2021

Just 2 questions on the feature:

1. The fix does not include compound select (like `union`). Is it on purpose ?

that's an oversight as the "add_cte()" method is part of what was added to CompoundSelect. If you can provide a short PR or open a new issue that would be helpful.

2. If someone wants to do it on a ORM query, it should be done like this `query.statement.add_cte()` ?

please use 2.0 style querying for new code.

@sqla-tester
Copy link
Collaborator

Eric Masseran has proposed a fix for this issue in the master branch:

Dispatch independent ctes on compound select https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2992

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
sql use case not really a feature or a bug; can be support for new DB features or user use cases not anticipated
Projects
None yet
Development

No branches or pull requests

3 participants