How do you pass a uuid list to a postgres SQL query?

App Building
1

Hi,

I currently have a shared query with a hardcoded list of user ids to filter out, ie. WHERE c.user_id NOT IN ('b5accf2f-b35e-45dc-982b-fadfa2c9acfe', 'c99396d7-91eb-4301-8d57-8a85f8d41388'). This works fine but I want to automate things so I created a variable and put the user ids in.

Eg

  1. userIds = 'b5accf2f-b35e-45dc-982b-fadfa2c9acee', 'c99396d7-91eb-4301-8d57-8a85f8d41387'

WHERE c.user_id NOT IN {{ userIds }}

error:"invalid input syntax for type uuid: "'b5accf2f-b35e-45dc-982b-fadfa2c9acee', 'c99396d7-91eb-4301-8d57-8a85f8d41387'""

So here it's interpreted as a string which obviously won't work.
Then tried asking ChatGPT and got this suggestion:

  1. userIds = ['b5accf2f-b35e-45dc-982b-fadfa2c9acee', 'c99396d7-91eb-4301-8d57-8a85f8d41387']

WHERE c.user_id != ALL (ARRAY[{{ userIds }}]::uuid)

error:"invalid input syntax for type uuid: "{"b5accf2f-b35e-45dc-982b-fadfa2c9acee","c99396d7-91eb-4301-8d57-8a85f8d41387"}""

Now I think it's because of double quotes. Does anyone know how to make them into single quotes (but still interpreted as an array) ?

2

Hi @magnus,

Can you give it a shot just using brackets? Like this:

WHERE c.user_id NOT IN ( {{ userIds }} )

(notice the {{ userIds }} is wrapped in brackets. See if that works?

3

Actually I missed to add them in my post. I assume you mean the parenthesis? Without them there's another error: * error:"syntax error at or near "$3"" and with them same error as in intial post, unfortunately.

Thanks for taking your time to reply anyhow!
Any other ideas?

It doesn't seem to exist a way in PostgreSQL to convert double quotes to single qoutes, otherwise that could be the solution. But if there's instead a way to force nr 2 above to output single quotes perhaps?

4

Hi @magnus,

Which array format did you use? It should work if you have an array like this:

userIds = ['b5accf2f-b35e-45dc-982b-fadfa2c9acee', 'c99396d7-91eb-4301-8d57-8a85f8d41387']

Then your query would be

WHERE c.user_id NOT IN ( {{ userIds }} )

One thing worth noting, depending on where userIds are stored, you may need to change their reference. For example, if they are from another query or something, they would usually be userIds.data, if they are in a variable, they might be userIds.value. If that's the case, you'd need to reference them more like this:

WHERE c.user_id NOT IN ( {{ userIds.data }} )

Does that work?

5

I'm having this variable defined inside the SQL editor in the Query Library, as in:
image so I don't think .value or .data is needed there. They are both undefined. As can be seen in this picture the array with uuids is shown but all are double quoted.

image

and error is still: error:"invalid input syntax for type uuid: "{"b5accf2f-b35e-45dc-982b-fadfa2c9acfe","c99396d7-91eb-4301-8d57-8a85f8d41388" ...

Does it make things clearer?

6

Since you are checking a value against and array, you should be using WHERE c.user_id <> ALL({{userIds}}) or WHERE NOT (c.user_id = ANY({{userIds}})).

The double quoting just means it is a string, which should be fine. You can run the following in a SQL block to play around with the concept:

with foo as (
SELECT 'a' val, ARRAY['b', 'c', 'd'] ary UNION ALL
SELECT 'b' val, ARRAY['a', 'b', 'c'] ary UNION ALL
SELECT 'c' val, ARRAY['a', 'b', 'd'] ary UNION ALL
SELECT 'd' val, ARRAY['b', 'c', 'd'] ary
)
SELECT *
FROM foo
WHERE val <> ALL(ary);
2 Likes
7

Thank you so much! That solved it! WHERE c.user_id <> ALL({{userIds}})

2 Likes