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

0
1 comment
Avatar
Permanently deleted user

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:

  • Global SQL Dialect
  • Project SQL Dialect

I set each to PostgreSQL, which resolved my issue. 

Cheers

1

Please sign in to leave a comment.