PostgreSQL нечувствителен к регистру SELECT для массива

Ответ 1

Одна из упомянутых альтернатив - установить расширение citext, поставляемое с PostgreSQL 8. 4+, и использовать массив citext:

regress=# CREATE EXTENSION citext;
regress=# SELECT 'foo' = ANY( '{"Foo","bar","bAz"}'::citext[] );
 ?column? 
----------
 t
(1 row)

Если вы хотите быть абсолютно правы в этом и избегать расширений, вы должны сделать несколько довольно уродливых подзапросов, потому что Pg не имеет много операций с расширенными массивами, в частности, нет операций с функциональным отображением. Что-то вроде:

SELECT array_agg(lower(($1)[n])) FROM generate_subscripts($1,1) n;

... где $ 1 - параметр массива. В вашем случае я думаю, что вы можете немного обмануть, потому что вы не заботитесь о сохранении порядка массива, поэтому вы можете сделать что-то вроде:

SELECT 'foo' IN (SELECT lower(x) FROM unnest('{"Foo","bar","bAz"}'::text[]) x);

Ответ 2

Мне кажется, это хаки, но я думаю, что он должен работать

SELECT value FROM table WHERE 'foo' = ANY(lower(value::text)::text[])

ilike могут иметь проблемы, если ваши массивы могут иметь _ или %

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

Обновить на основе подзапроса ниже, один вариант будет простой функцией:

CREATE OR REPLACE FUNCTION lower(text[]) RETURNS text[] LANGUAGE SQL IMMUTABLE AS
$$
SELECT array_agg(lower(value)) FROM unnest($1) value;
$$;

Тогда вы могли бы сделать:

SELECT value FROM table WHERE 'foo' = ANY(lower(value));

Это действительно лучший подход. Вы также можете создавать индексы GIN на выходе функции, если хотите.

Ответ 3

Другой вариант: unnest()

WITH tbl AS (SELECT 1 AS id, '{"Foo","bar","bAz"}'::text[] AS value)

SELECT value
FROM  (SELECT id, value, unnest(value) AS val FROM tbl) x
WHERE  lower(val) = 'foo'
GROUP  BY id, value;

Я добавил столбец id, чтобы получить точно идентичные результаты - т.е. дублировать value, если в базовой таблице есть дубликаты. В зависимости от ваших обстоятельств вы можете, возможно, опустить id из запроса, чтобы свернуть дубликаты в результатах или если нет совпадений для начала. Также демонстрирует альтернативу синтаксиса:

SELECT value
FROM  (SELECT value, lower(unnest(value)) AS val FROM tbl) x
WHERE  val = 'foo'
GROUP  BY value;

Если элементы массива уникальны в массивах в нижнем регистре, вам даже не нужен GROUP BY, так как каждый value может только совпадать один раз.

SELECT value
FROM  (SELECT value, lower(unnest(value)) AS val FROM tbl) x
WHERE  val = 'foo';

'foo', очевидно, должен быть строчным. Быстро.

Если вы хотите, чтобы быстрая остроумия была большой таблицей, я бы создал функциональный индекс GIN.

Ответ 4

Мое решение исключить значения с помощью подбора select...

and groupname not ilike all (
    select unnest(array[exceptionname||'%'])
    from public.group_exceptions
    where ...
      and ...
      )

Ответ 5

Регулярное выражение может сделать работу для большинства случаев

SELECT array_to_string ('{"a", "b", "c"}' :: text [], '|') ~ * ANY ('{"A", "B", "C"}');