arrays - existe - query array postgresql

Compruebe si el valor existe en la matriz de Postgres

Question

Necesito una forma de comprobar si existe un valor en una matriz determinada. Hasta ahora se me ocurrió algo como esto

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

pero sigo pensando que debería haber una forma más simple de hacerlo, simplemente no puedo verlo.

Editar: me di cuenta de que podía hacer esto

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

Esto es mucho mejor y creo que será suficiente, pero si tiene otras formas de hacerlo, comparta.

Answer 1

pero si tienes otras formas de hacerlo, comparte.

Puedes comparar dos matrices. Si alguno de los valores en el conjunto izquierdo se superpone con los valores en el conjunto de la derecha, entonces devuelve verdadero. Es un poco hackish, pero funciona.

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

• En la primera y segunda consulta, el valor 1 está en la matriz correcta
• Tenga en cuenta que la segunda consulta es true , aunque el valor 4 no se encuentre en la matriz correcta
• Para la tercera consulta, no hay valores en la matriz izquierda (es decir, 4 ) en la matriz correcta, por lo que devuelve false

Answer 2

Cuando se busca la existencia de un elemento en una matriz, se requiere la conversión adecuada para pasar el analizador SQL de postgres. Aquí hay una consulta de ejemplo que utiliza el operador array contains en la cláusula join:

Para simplificar, solo enumero la parte relevante:

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

La parte de unión de SQL se muestra

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

Lo siguiente también funciona

on t2.panel = ANY(t1.other_name)

Solo estoy adivinando que se requiere el lanzamiento extra porque el análisis no tiene que buscar la definición de la tabla para calcular el tipo exacto de la columna. Otros por favor comentan esto.

Answer 3

Cuidado con la trampa en la que me metí: cuando compruebes si cierto valor no está presente en una matriz, no debes hacer:

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

pero usa

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

en lugar.

Answer 4

Más simple con la construcción ANY :

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

El operando derecho de ANY (entre paréntesis) puede ser un conjunto (resultado de una subconsulta, por ejemplo) o una matriz . Hay varias formas de usarlo:

• SQLAlchemy: cómo filtrar en los tipos de columna PgArray?

Diferencia importante : los operadores de matriz ( <@ , @> y otros) esperan tipos de matriz como operandos y admiten índices GIN o GiST en la distribución estándar de PostgreSQL, mientras que la construcción ANY espera un tipo de elemento como operando izquierdo y no admite estos índices . Ejemplo:

• Índice para encontrar un elemento en una matriz JSON

Nada de esto funciona para elementos NULL . Para probar NULL :

• Compruebe si existe NULL en la matriz de Postgres

Answer 5

unnest se puede usar. Extiende la matriz a un conjunto de filas y luego simplemente verifica que un valor existe o no es tan simple como usar IN o NOT IN .

p.ej

1. id => uuid

2. exception_list_ids => uuid []

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