into - PostgreSQL mayúscula insensible a la matriz

recorrer array postgresql

Question

Estoy teniendo problemas para encontrar la respuesta aquí, en google o en los documentos ...
Necesito hacer una selección que no distinga mayúsculas y minúsculas contra un tipo de matriz.

Así que si:

value = {"Foo","bar","bAz"}

Necesito

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

para emparejar

He intentado muchas combinaciones de lower () sin éxito.

ILIKE lugar de = parece funcionar pero siempre he estado nervioso por LIKE , ¿es la mejor manera?

Answer 1

A mí me parece una piratería, pero creo que debería funcionar.

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

ilike podría tener problemas si sus arreglos pueden tener _ o %

Tenga en cuenta que lo que está haciendo es convertir la matriz de texto en una sola cadena de texto, convertirla en minúsculas y luego volver a una matriz. Esto debería ser seguro. Si esto no es suficiente, podría usar varias combinaciones de string_to_array y array_to_string, pero creo que las representaciones textuales estándar deberían ser más seguras.

A continuación, actualice la solución de subconsultas, una opción sería una función simple:

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

Entonces podrías hacer:

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

Este podría ser el mejor enfoque. También puede crear índices GIN en la salida de la función si lo desea.

Answer 2

Mi solución para excluir valores usando una selección secundaria ...

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

Answer 3

Otra alternativa sería con un poco 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;

Agregué una columna de id para obtener resultados exactamente idénticos, es decir, un value duplicado si hay duplicados en la tabla base. Dependiendo de sus circunstancias, es probable que pueda omitir el id de la consulta para colapsar los duplicados en los resultados o si no hay duplicados para empezar. También demostrando una alternativa de sintaxis:

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

Si los elementos de la matriz son únicos dentro de las matrices en minúsculas, ni siquiera necesita el GROUP BY , ya que cada value solo puede coincidir una vez.

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

''foo'' debe ser minúscula, obviamente.
Debería ser rápido .

Sin embargo, si desea que sea rápido con una tabla grande, crearía un índice GIN funcional .

Answer 4

Una alternativa que no se menciona es instalar la extensión de citext que viene con PostgreSQL 8.4+ y usar una matriz de citext :

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

Si quiere ser estrictamente correcto al respecto y evitar las extensiones, tiene que hacer algunas subconsultas bastante feas porque Pg no tiene muchas operaciones de matriz enriquecida, en particular ninguna operación de mapeo funcional. Algo como:

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

... donde $ 1 es el parámetro de matriz. En su caso, creo que puede hacer un poco de trampa porque no le importa conservar el orden de la matriz, por lo que puede hacer algo como:

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