Is it possible to use INSERT statements inside a CTE (WITH query) +++ another question?

In PostgreSQL, you're allowed to use other DML statements (e.g., insert, update, delete) inside a CTE. Something like:

sql 
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
)
INSERT INTO products_log
SELECT * FROM moved_rows;

sql 
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
)
INSERT INTO products_log
SELECT * FROM moved_rows;

It's also optional if you want to include RETURNING or not.

1st question is, can this kind of query be accomplished in Drizzle? Or is it only limited to SELECT statements?

2nd question is, how can I execute a transaction in Drizzle in such a way that the whole transaction block is sent as one whole statement to the database - in just one round trip? Something similar to LibSQL Batch API. For this use case, I don't have any JavaScript code/logic between each statement inside the transaction. (I'm using PostgreSQL)

Current behavior I've observed is that each statement incurs a round trip. So if there are around 5 statements inside the transaction, then I think that corresponds to 7 total trips to the database (5 + including BEGIN and COMMIT). I want to reduce latency.

Thanks!

https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

PostgreSQL Documentation

7.8. WITH Queries (Common Table Expressions)

7.8. WITH Queries (Common Table Expressions) # 7.8.1. SELECT in WITH 7.8.2. Recursive Queries 7.8.3. Common Table Expression Materialization 7.8.4. Data-Modifying …

Drizzle Team•3y ago

brynthrax

Is it possible to use INSERT statements inside a CTE (WITH query) +++ another question?

In PostgreSQL, you're allowed to use other DML statements (e.g., insert, update, delete) inside a CTE. Something like:

sql 
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
)
INSERT INTO products_log
SELECT * FROM moved_rows;

sql 
WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
)
INSERT INTO products_log
SELECT * FROM moved_rows;

PostgreSQL Documentation

7.8. WITH Queries (Common Table Expressions)

7.8. WITH Queries (Common Table Expressions) # 7.8.1. SELECT in WITH 7.8.2. Recursive Queries 7.8.3. Common Table Expression Materialization 7.8.4. Data-Modifying …

Is it possible to use INSERT statements inside a CTE (WITH query) +++ another question?

Is it possible to use INSERT statements inside a CTE (WITH query) +++ another question?

Similar Threads

Similar Threads

Similar Threads