Postgres WITH (CTE) statements not parsed correctly [Edit: Solved]
Edit: See my post below for the solution. It turns out that it's helpful to tell DataGrip to use the correct dialect for your database :)
--------------------
I'm having trouble executing WITH statements against a Postgres 9.6 database using DataGrip. The same statements work just fine in any other client I use (tested against psql and PgAdmin4), but I receive a syntax error when I run the same in DataGrip.
Example:
WITH cte AS (
SELECT person_id, count("data") AS data
FROM table
GROUP BY person_id
)
UPDATE another_table
SET updated_data = cte.data
FROM cte
WHERE another_table."Person Id" = cte.person_id
;
When I run the code, I initially receive a syntax error (ERROR: syntax error at end of input) that references line 5 (the end of the CTE). If I tell DataGrip to Ignore the error, I get another error message (ERROR: relation "cte" does not exist). Looking at the Output section, it's clear that DataGrip is separating the two statements and trying to run them separately from one another:
sql> WITH cte AS (
SELECT person_id, count("data") AS data
FROM table
GROUP BY person_id
)
[2017-09-19 11:49:54] [42601] ERROR: syntax error at end of input
[2017-09-19 11:49:54] Position: 147
sql> UPDATE another_table
SET updated_data = cte.data
FROM cte
WHERE another_table."Person Id" = cte.person_id
[2017-09-19 11:55:46] [42P01] ERROR: relation "cte" does not exist
[2017-09-19 11:55:46] Position: 74
How do I force DataGrip to treat these as a single block of code to execute together? Again, this code runs perfectly fine in psql and PgAdmin, but I really prefer DataGrip as an IDE.
(Nitpicker note: The position #s refer to the code that actually ran, not the sanitized version I'm putting here. Sorry if that creates confusion!)
Thanks in advance to anyone who can help with this!
Cheers
请先登录再写评论。
And now to solve my own problem: The root issue was that the SQL Dialect for the project I was in was set to Redshift. Changing this value back to PostgreSQL fixes the issue.
For the benefit of others who run into the same issue:
Preferences > Database > SQL Dialects:
I set each to PostgreSQL, which resolved my issue.
Cheers