SELECT statement from a list of values as a temporary table

Community
,
1

I’d like to be able to get the same result as the following query using the QuestDB connector for SQLAlchemy:

SELECT 
    MyData.Column1, 
    MyData.Column2 
FROM (VALUES(1, 'Tom'),
            (2, 'Dick'),
            (3, 'Harry'),
            (4, 'Ermintrude')
      ) MyData(Column1, Column2)

Using something like this as a parameter:

rows = [
    (1, "Tom" ),
    (2, "Dick"),
    (3, "Harry"),
    ...
]

Currently the only way I found is building a long list of UNION ALL statements divided in chunks.

That’s very tedious and also produces big query!

If QuestDB supported the former SQL syntax I could just use expanding bind parameters provided by SQLAlchemy.

Is there any other way or could this feature be implemented (select from list of literal values as a CTE)?

Thank you a lot and have a great day!

2

I am afraid that I cannot think of a better solution. Just commenting here so you know we are not ignoring your question, it is just to the best of my knowledge a UNION ALL is right now the only option here

3

Could you open an issue on Github if there isn’t already? As it is something in my firm that we would strongly need (performance and code readability) we might contribute to it.

Best regards, Luca.

4

Hi @LucaBonaldoIT ,

The relevant issue is here: Postgres VALUES syntax for temporary tables with hardcoded values · Issue #4927 · questdb/questdb · GitHub

We have discussed this recently. We are releasing a revised version of the PG Wire driver soon. After this, we intend to make more changes around our Postgres compatibility, during which we may sequence this feature.

We take contributions and could support you on this. Feel free to connect on Slack/GitHub to discuss it further! :slight_smile: