Common Table Expressions for PostgreSQL?

Hi!

New user, enjoying Metabase so far. But, I’ve run into an issue: I can’t seem to get Common Table Expressions (aka “WITH” queries) to work when making raw SQL queries.

For example:

WITH sub_query AS (select * from users limit 1) select * from sub_query;

That works totally fine through a normal connection to the database (and indeed, through other competing projects like SQLPad), but throws the following error in Metabase:

Syntax error in SQL statement " WITH TESTING_QUERY AS[*] (SELECT * FROM USERS LIMIT 1) SELECT * FROM TESTING_QUERY; "; expected "., ("; SQL statement: -- Metabase:: userID: 1 executionID: 2d597259-eed2-4546-b754-3f6a189ab56c queryType: native queryHash: 1582907852 with testing_query as (select * from users limit 1) select * from testing_query; [42001-193]

Am I missing something, or does Metabase not allow Common Table Expressions in queries?

We currently only let you execute one query per “question”. It’s a limitation of the JDBC connector we haven’t worked around.

Ahhh, I see. That’s too bad, I was hoping we could finally transition off of SQLPad. Is there a ticket somewhere I could subscribe to track this, or is it not really on the roadmap?

Not sure if there’s an open issue. You should open one at github.com/metabase/metabase/issues/new if you could.

I’ll close it as a dupe if I find an earlier one.

Done! https://github.com/metabase/metabase/issues/4555

Following up here: diagnosed this is not a real issue! The problem was the dropdown for the database selection was “stuck” and in actuality I was querying the Sample Database instead of my PostgreSQL instance, thus resulting in the CTE error.

Once I rebooted Metabase this issue went away and I was able to use CTEs.

awesome. disregard everything I said =)