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

Question

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

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

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

Редактировать: Просто понял, что я мог сделать это

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

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

Answer 1

Проще с ANY построить:

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

Правильный операнд ANY (в скобках) может быть либо набором (например, результатом подзапроса), либо массивом. Есть несколько способов его использования:

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

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

• Индекс для поиска элемента в массиве JSON

Ничто из этого не работает для NULL элементы. Для проверки на NULL:

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

Answer 2

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

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

но использовать

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

вместо.

Answer 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

Answer 4

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

например

1. id => uuid

2. exception_list_ids => uuid []

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

Answer 5

Привет, у меня отлично работает, может быть, кому-то пригодится

выберите * из your_table, где array_column::text ilike ANY (ARRAY['%text_to_search%'::text]);

Answer 6

"Любой" работает хорошо. Просто убедитесь, что ключевое слово any находится справа от знака равенства, т. Е. Стоит после знака равенства.

Приведенный ниже оператор вызовет ошибку: ОШИБКА: синтаксическая ошибка рядом с "любым"

select 1 where any('{hello}'::text[]) = 'hello';

В то время как ниже пример работает нормально

select 1 where 'hello' = any('{hello}'::text[]);

Answer 7

При поиске существования элемента в массиве требуется правильное приведение для передачи анализатора SQL postgres. Вот один пример запроса с использованием оператора array в предложении 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)

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

Answer 8

Дополнительное примечание к принятому ответу @ErwinBrandstetter: если вы хотите воспользоваться индексом столбца массива при сравнении с одним значением, вы можете обернуть свое значение в массив следующим образом:

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

Однако вам может потребоваться указать тип массива при использовании параметров:

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