Support of MERGE in CTE with Postgresql 17 #995
Description
I work on a RESTful API with PostgreSQL and SQLPage.
I need to use MERGE in a CTE for implementing the PUT method. I got the error message:
2025-08-20T08:15:21.070Z ERROR sqlpage::webserver::database::execute_queries] Parsing failed: SQLPage couldn't understand the SQL file. Please check for syntax errors:
WITH merged_row AS (
⬆️
MERGE INTO users AS cible
Caused by:
0: sql parser error: Expected: SELECT, VALUES, or a subquery in the query body, found: MERGE at Line: 2, Column: 5
1: "page.sql" contains a syntax error preventing SQLPage from parsing and preparing its SQL statements.
[2025-08-20T08:15:21.071Z INFO actix_web::middleware::logger] 127.0.0.1 "GET /page.sql HTTP/1.1" 200 2311 "-" "vscode-restclient" 0.001238
The code below is the code executed that triggers the error :
WITH merged_row AS (
MERGE INTO users AS cible
USING (
SELECT
'john.doe' AS username,
'password' AS password,
'xxxxxxxxxx' AS uuid,
TRUE as is_active
) AS source
ON cible.uuid = source.uuid
WHEN MATCHED THEN
UPDATE SET
username = source.username,
password = source.password,
uuid = source.uuid,
is_active = source.is_active
WHEN NOT MATCHED THEN
INSERT (username, password, uuid, is_active)
VALUES (source.username, source.password, source.uuid, source.is_active)
RETURNING *
)
SELECT
'debug' AS component,
COUNT(*) AS count
FROM merged_row;The SQL query works perfectly in PostgreSQL 17. Is this a limitation of SQLPage's SQL parser?
Best regards
Olivier