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

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
3
  • 1
    It may be worth noting that a GIN index can speed this up. Commented May 25, 2021 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; ? Commented May 25, 2021 at 16:49
  • You can not add an index to a view. Commented May 26, 2021 at 5:05

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.