sqlpostgresqlmergecommon-table-expressionpostgresql-16

Use MERGE with CTE


Does Postgresql allow use of cte as source of the MERGE instruction ?

CREATE TABLE IF NOT EXISTS graph
(
    idgraph SERIAL PRIMARY KEY,
    from_table varchar(128)
);

WITH cte2 AS 
(
    SELECT 'tag' AS from_table
)
MERGE INTO graph g
USING cte2 ON g.from_table = cte2.from_table 

WHEN NOT MATCHED BY TARGET 
    THEN
        INSERT(from_table) 
        VALUES(cte2.from_table)

WHEN NOT MATCHED BY SOURCE 
    THEN
        DELETE;

What the Postgresql doc says about CTEs:

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

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

What https://extendsclass.com/postgresql-online.html says:

syntax error at or near "MERGE"

And pglite does produce an error message:

syntax error at or near "BY"

Who should I trust ? I am using pglite wasm in browser.

Here is the version info:

PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by emcc (Emscripten gcc/clang-like replacement + linker emulating GNU ld) 3.1.68 (ceee49d2ecdab36a3feb85a684f8e5a453dde910), 32-bit"


Solution

  • This could be causing issues with version 17 too:
    From the docs:

    The WHEN clause may specify WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, or WHEN NOT MATCHED [BY TARGET]. Note that the SQL standard only defines WHEN MATCHED and WHEN NOT MATCHED (which is defined to mean no matching target row). WHEN NOT MATCHED BY SOURCE is an extension to the SQL standard, as is the option to append BY TARGET to WHEN NOT MATCHED, to make its meaning more explicit.

    ... and a warning from the docs:

    Only columns from the target table that attempt to match data_source rows should appear in join_condition. join_condition subexpressions that only reference the target table's columns can affect which action is taken, often in surprising ways.

    If both WHEN NOT MATCHED BY SOURCE and WHEN NOT MATCHED [BY TARGET] clauses are specified, the MERGE command will perform a FULL join between data_source and the target table. For this to work, at least one join_condition subexpression must use an operator that can support a hash join, or all of the subexpressions must use operators that can support a merge join.

    More about it

    WITH cte2 AS 
      ( SELECT 'tag' AS from_table )
    MERGE INTO graph g
    USING cte2 ON(g.from_table = cte2.from_table)
    WHEN NOT MATCHED THEN
    INSERT(from_table) VALUES(cte2.from_table)
    
    Select * From graph
    

    Result:

    idgraph from_table
    1 tag

    fiddle