PostgreSQL – Query Array Column with Array

arraypostgresqlquery

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:

SELECT * 
FROM people 
WHERE codes && array[5294,1933]::bigint[]

Alternatively use a string that uses {..}

SELECT * 
FROM people 
WHERE codes && '{5294,1933}'::bigint[]

I prefer the first one, as that also doesn't have problems when the data type itself requires quoting e.g. for text arrays.