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.
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?
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:
I'm having this variable defined inside the SQL editor in the Query Library, as in: 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.
and error is still: error:"invalid input syntax for type uuid: "{"b5accf2f-b35e-45dc-982b-fadfa2c9acfe","c99396d7-91eb-4301-8d57-8a85f8d41388" ...
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);