0

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

2

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.

3
  • 1
    It may be worth noting that a GIN index can speed this up. May 25 at 6:43
  • Is adding an index to a view the same as to a table? Would it look like: CREATE OR REPLACE VIEW people AS SELECT (...) FROM ... WHERE ... CREATE INDEX idx_codes ON people(codes) USING GIN; ? May 25 at 16:49
  • You can not add an index to a view. May 26 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.