I Have a Delete-Insert Cte That Fails in a Strange Manner

I have a delete-insert CTE that fails in a strange manner

In the first example you provide a yet untyped NULL to the INSERT statement.

In the second example you provide NULL one step earlier (in the CTE), the expression has to be typed and is assigned the type unknown. For other constants (like numeric constants: 123), Postgres can derive a more fitting default data type, but NULL (or a string literal 'foo') could be anything. And there is no type conversion defined between unknown and json.

Cast NULL to the right data type in the CTE to avoid the problem (as you found yourself by now).

Or use text as stepping stone in the casting chain if it's too late for that. Everything can be cast to / from text.

You can simplify your demo to the following:

Works:

SELECT NULL::json;

Fails:

SELECT new_data::json
FROM  (SELECT NULL AS new_data) t;

Works again:

SELECT new_data
FROM  (SELECT NULL::json AS new_data) t;

Or:

SELECT new_data::text::json
FROM  (SELECT NULL AS new_data) t;

Why PostgreSQL CTE with DELETE is not working?

Here, DELETE is working in the way it was designed to work. The answer is actually pretty straightforward and documented. I've experienced the same behaviour years ago.

The reason your delete is not actually removing the data is because your where condition doesn't match with what's stored inside the table as far as what the delete statement sees.

All sub-statements within CTE (Common Table Expression) are executed with the same snapshot of data, so they can't see other statement effect on target table. In this case, when you run UPDATE and then DELETE, the DELETE statement sees the same data that UPDATE did, and doesn't see the updated data that UPDATE statement modified.

How can you work around that? You need to separate UPDATE & DELETE into two independent statements.

In case you need to pass the information about what to delete you could for example (1) create a temporary table and insert the data primary key that has been updated so that you can join to that in your latter query (DELETE based on data that was UPDATEd). (2) You could achieve the same result by simply adding a column within the updated table and changing its value to mark updated rows or (3) however you like it to get the job done. You should get the feeling of what needs to be done by above examples.

Quoting the manual to support my findings:
7.8.2. Data-Modifying Statements in WITH

The sub-statements in WITH are executed concurrently with each other
and with the main query. Therefore, when using data-modifying
statements in WITH, the order in which the specified updates actually
happen is unpredictable. All the statements are executed with the same
snapshot (see Chapter 13), so they cannot “see” one another's effects
on the target tables.

(...)
This also applies to deleting a row that was already updated in the same statement: only the update is performed

Is it safe to insert data from inside of a CTE expression?

The manual page on WITH queries states that your use case is legitimate and supported:

You can use data-modifying statements (INSERT, UPDATE, or DELETE) in WITH.

and

... data-modifying statements are only allowed in WITH clauses that are attached to the top-level statement. However, normal WITH visibility rules apply, so it is possible to refer to the WITH statement's output from the sub-SELECT.

Further:

If a data-modifying statement in WITH lacks a RETURNING clause, then it forms no temporary table and cannot be referred to in the rest of the query. Such a statement will be executed nonetheless.

Error in using with statement with PostgreSQL insert query

A CTE (WITH clause) is part of an SQL statement and cannot stand on its own. PostgreSQL complains about an error at the end of the statement, because it expects a following SELECT, INSERT, UPDATE or DELETE.

In a way, a CTE is like a view defined only for a single statement. You cannot define a CTE and then use it with several statements; for that, you could define a temporary view in the pg_temp schema.

Use a CTE to UPDATE or DELETE in MySQL

This appears to be a published bug in MySQL 8.x. From this bug report:

In the 2015 version of the SQL standard, a CTE cannot be defined in UPDATE; MySQL allows it but makes the CTE read-only (we're updating the documentation now to mention this).
This said, one could use a view instead of the CTE; then the view may be updatable, but due to the presence of window functions it is materialized into a temporary table (it is not merged) so is not updatable (we're going to mention it in the doc as well).

All the above applies to DELETE too.

If you follow the above bug link, you will see a workaround suggested for using a CTE, but it involved joining the CTE to the original target table in a one-to-one mapping. Based on your example, which is a blanket delete, it is not clear what workaround you need, were to proceed using a CTE for your delete.

mysql cte . using with and insert it alerts a syntax error

Use insert . . . select:

INSERT INTO flow_instances (custom_id_year, custom_id_counter)
WITH this_year AS (
      SELECT YEAR(CURDATE()) as this_year
     ),
     max_val AS (
      SELECT COALESCE(MAX(custom_id_counter), 0) as max_val
      FROM flow_instances AS max_val
      WHERE custom_id_year = YEAR(CURDATE())
     )
    SELECT ty.this_year, mv.max_val + 1
    FROM this_year ty CROSS JOIN
         max_val mv;

You need to reference the CTEs in order to use the values they define.

---

Related Topics