Question 188979 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Element in list-of-elements when element is an array

What would be the correct syntax in Postgres to do something like:

SELECT ARRAY[4,5,6] IN ARRAY[[4,5,6],[7,8,9]];

Some dbs have a list_contains or IN UNNEST(arr_expr) to do something like the above but I haven’t been having luck checking in Postgres docs.

Tags:

2

Answers


  1. 0

    If your containing array has sub-arrays all the same length you should be able to use the array-contains operator

    richardh=> SELECT ARRAY[1,2,3] <@ ARRAY[ARRAY[1,2,3], ARRAY[4,5,6]];
 ?column? 
----------
 t
(1 row)

    However, be aware it is more "generous" in its matching than you might want

    richardh=> SELECT ARRAY[1,2,3] <@ ARRAY[ARRAY[1,2,3,4], ARRAY[4,5,6,7]];
 ?column? 
----------
 t
(1 row)

    See the manuals for details.

    Login or Signup to reply.
  2. 0

    UNNEST "flattens" multidimensional arrays, so it is not useful here

    array-contains (<@ operator) also internally "flattens" such arrays

    You can try to store your data as JSON(B) and it will work as expected:

    SELECT count(*)
FROM jsonb_array_elements('[[4,5,6],[7,8,9]]'::jsonb) arr_rows
WHERE arr_rows = '[4,5,6]'::jsonb;

SELECT count(*)
FROM jsonb_array_elements('[[4,5,6],[7,8,9]]'::jsonb) arr_rows
WHERE arr_rows = '[4,5,6,7]'::jsonb;

SELECT count(*)
FROM jsonb_array_elements('[[4,5,6],[7,8,9]]'::jsonb) arr_rows
WHERE arr_rows = '[4,5]'::jsonb;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search