Проверьте, существует ли значение в массиве Postgres

Мне нужен способ проверить, существует ли значение в заданном массиве. До сих пор я придумал что-то вроде этого

select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int)

но я продолжаю думать, что должен быть более простой способ этого, я просто не могу его увидеть.

Изменить: просто понял, что я могу это сделать

select '{1,2,3}'::int[] @> ARRAY[value_variable::int]

Это намного лучше, и я считаю, что этого хватит, но если у вас есть другие способы сделать это, пожалуйста, поделитесь.

Ответ 1

Упрощение с помощью ANY:

SELECT value_variable = ANY ('{1,2,3}'::int[])

Правильный операнд ANY (между круглыми скобками) может быть либо set (результат подзапроса, например), либо < массив href= "http://www.postgresql.org/docs/current/interactive/functions-comparisons.html#AEN19712" rel= "noreferrer" > . Существует несколько способов его использования:

SQLAlchemy: как фильтровать типы столбцов PgArray?

Важная разница: Операторы массива (<@, @> и др.) ожидают типы массивов как операнды и поддерживают индексы GIN или GiST в стандартном распределении PostgreSQL, а конструкция ANY ожидает, что тип элемента будет левым операндом и не будет поддерживать эти индексы. Пример:

Указатель для поиска элемента в массиве JSON

Ничего из этого не работает для элементов NULL. Проверить NULL:

Проверьте, существует ли NULL в массиве Postgres

Ответ 2

Следите за ловушкой, в которую я попал: проверяя, нет ли определенного значения в массиве, вы не должны делать:

SELECT value_variable != ANY('{1,2,3}'::int[])

но используйте

SELECT value_variable != ALL('{1,2,3}'::int[])

вместо.

Ответ 3

но если у вас есть другие способы сделать это, пожалуйста, поделитесь.

Вы можете сравнить два массива. Если какое-либо из значений в левом массиве перекрывает значения в правильном массиве, оно возвращает true. Это своего рода хакерство, но оно работает.

SELECT '{1}'   && '{1,2,3}'::int[];  -- true
SELECT '{1,4}' && '{1,2,3}'::int[];  -- true
SELECT '{4}'   && '{1,2,3}'::int[];  -- false

В первом и втором запросах значение 1 находится в правильном массиве
Обратите внимание, что второй запрос true, хотя значение 4 не содержится в правильном массиве
Для третьего запроса в правом массиве нет значений (т.е. 4), поэтому он возвращает false

Ответ 4

unnest. Он расширяет массив до набора строк, а затем просто проверка значения существует или нет, так же просто, как использование IN или NOT IN.

например.

id = > uuid
exception_list_ids = > uuid []

select * from table where id NOT IN (select unnest(exception_list_ids) from table2)

Ответ 5

При поиске существования элемента в массиве требуется правильное кастинг для передачи парсера SQL postgres. Вот один пример запроса с использованием массива содержит оператор в предложении join:

Для простоты я перечисляю только соответствующую часть:

table1 other_name text[]; -- is an array of text

Часть соединения SQL, показанная

from table1 t1 join table2 t2 on t1.other_name::text[] @> ARRAY[t2.panel::text]

Также работает

on t2.panel = ANY(t1.other_name)

Я просто догадываюсь, что требуется дополнительное кастинг, потому что для синтаксического анализа не нужно отображать определение таблицы для определения точного типа столбца. Другие, пожалуйста, прокомментируйте это.