I have a postgres view "people_codes" that has a column that is an ARRAY_AGG of intergers. I am trying to query on the view with an array of integers for all rows that may have one of these codes
uuid | name | codes
(uuid) | (varchar) | (bigint[])
abc-def | john | {5294, 5295}
bee-aaa | jane | {1123, 2222}
111-a98 | bob | {1933}
I can successfully query the view with only one integer for the query
SELECT * FROM people_codes WHERE 5294 = ANY (codes);
And this returns the row that I need:
abc-def | john | {5294, 5295} |
My final goal is to be able to query my view with an array, so that I can find all rows that have one or more of the given codes.
I have tried changing my value into an int, also casting it as a ::bigint[], but I get a syntax error at the brackets. Occurs no matter the brackets (, [, or {
SELECT * FROM people WHERE [5294,1933]::bigint[] = ANY (codes);
ERROR: syntax error at or near "["
LINE 6: SELECT * FROM people WHERE [5294,1933]::bigint...
I have also tried using the @> and && comparison operators, but having trouble with the array of values again. It's trying to hint to adding an explicit type cast, which is what I thought was doing. Same thing with trying to place the search values into an ARRAY_AGG
SELECT * FROM people WHERE codes @> IN (5294,1933)::bigint[];
ERROR: operator does not exist: bigint[] @>
LINE 8: ...ROM people WHERE codes @> IN (529...
What is a correct query to get rows that contain any elements in the query array? By searching on the Array of [5294,1933] I'm expecting to get back the rows for both john and bob
Best Answer
As documented in the manual there are two ways to specify an array constant
On is to use the
array[...]
notation:Alternatively use a string that uses
{..}
I prefer the first one, as that also doesn't have problems when the data type itself requires quoting e.g. for
text
arrays.