Vérifier si NULL existe dans le tableau de Postgres

si vous sachez un seul élément qui ne peut jamais exister dans vos tableaux, vous pouvez utiliser cette fast expression dans Postgres 9.1 (ou n'importe quelle version de Postgres). Dites que vous avez un tableau de nombres positifs, donc -1 ne peut pas y être:

-1 = ANY(ar) IS NULL

réponse connexe avec explication détaillée:

• Est la matrice de toutes les valeurs Null dans PostgreSQL

Si vous ne peut pas être absolument sûr , vous pourrait revenir à l'un des cher, mais safe méthodes unnest() . Comme:

(SELECT bool_or(x IS NULL) FROM unnest(ar) x)

ou:

EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)

mais vous pouvez avoir rapide et sûr avec un CASE expression. Utilisez un nombre peu probable et revenez à la méthode sûre si elle devrait exister. Vous pouvez vouloir traiter le cas ar IS NULL séparément. Voir démonstration ci-dessous.

---

Postgres 9.1 est en train de vieillir. Envisager la mise à niveau vers une version actuelle. En Postgres 9.3 ou plus tard, vous pouvez tester avec la fonction intégrée array_remove() ou array_replace() .

ou vous pouvez le faire fonctionner avec array_position() dans Postgres 9.5 ou plus tard comme @Patrick fourni . J'ai ajouté des variantes améliorées ci-dessous.

Démo

SELECT num, ar, expect
     , -1 = ANY(ar) IS NULL                                   AS t_1   --  50 ms
     , (SELECT bool_or(x IS NULL) FROM unnest(ar) x)          AS t_2   -- 754 ms
     , EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)    AS t_3   -- 521 ms
     , CASE -1 = ANY(ar)
         WHEN FALSE THEN FALSE
         WHEN TRUE THEN EXISTS (SELECT 1 FROM unnest(ar) x WHERE x IS NULL)
         ELSE NULLIF(ar IS NOT NULL, FALSE)  -- catch ar IS NULL       --  55 ms
      -- ELSE TRUE  -- simpler for columns defined NOT NULL            --  51 ms
       END                                                    AS t_91
     , array_replace(ar, NULL, 0) <> ar                       AS t_93a --  99 ms
     , array_remove(ar, NULL) <> ar                           AS t_93b --  96 ms
     , cardinality(array_remove(ar, NULL)) <> cardinality(ar) AS t_94  --  81 ms
     , COALESCE(array_position(ar, NULL::int), 0) > 0         AS t_95a --  49 ms
     , array_position(ar, NULL) IS NOT NULL                   AS t_95b --  45 ms
     , CASE WHEN ar IS NOT NULL
            THEN array_position(ar, NULL) IS NOT NULL END     AS t_95c --  48 ms
FROM  (
   VALUES (1, '{1,2,NULL}'::int[], true)     -- extended test case
        , (2, '{-1,NULL,2}'      , true)
        , (3, '{NULL}'           , true)
        , (4, '{1,2,3}'          , false)
        , (5, '{-1,2,3}'         , false)
        , (6, NULL               , null)
   ) t(num, ar, expect);

résultat:

 num |     ar      | expect | t_1    | t_2  | t_3 | t_91 | t_93a | t_93b | t_94 | t_95a | t_95b | t_95c
-----+-------------+--------+--------+------+-----+------+-------+-------+------+-------+-------+-------
   1 | {1,2,NULL}  | t      | t      | t    | t   | t    | t     | t     | t    | t     | t     | t
   2 | {-1,NULL,2} | t      | f --!! | t    | t   | t    | t     | t     | t    | t     | t     | t
   3 | {NULL}      | t      | t      | t    | t   | t    | t     | t     | t    | t     | t     | t
   4 | {1,2,3}     | f      | f      | f    | f   | f    | f     | f     | f    | f     | f     | f
   5 | {-1,2,3}    | f      | f      | f    | f   | f    | f     | f     | f    | f     | f     | f
   6 | NULL        | NULL   | t --!! | NULL | f   | NULL | NULL  | NULL  | NULL | f     | f     | NULL

noter que array_remove() et array_position() ne sont pas autorisés pour tableaux multidimensionnels . Toutes les expressions de la à droite de t_93a seulement travailler pour les tableaux 1-dimensioal.

plus d'essais dans ce violon SQL (pour Postgres 9.3).

de Référence de l'installation

L'ajout de test de référence avec 200k lignes dans Postgres 9.5 . C'est ma configuration:

CREATE TEMP TABLE t AS
SELECT row_number() OVER() AS num
     , array_agg(elem) AS ar
     , bool_or(elem IS NULL) AS expected
FROM  (
   SELECT CASE WHEN random() > .95 THEN NULL ELSE g END AS elem  -- 5% NULL VALUES
        , count(*) FILTER (WHERE random() > .8)
                   OVER (ORDER BY g) AS grp  -- avg 5 element per array
   FROM   generate_series (1, 1000000) g  -- increase for big test case
   ) sub
GROUP  BY grp;

fonction wrapper

pour utilisation répétée , je créerais une fonction dans Postgres 9.5 comme ceci:

CREATE OR REPLACE FUNCTION f_array_has_null (anyarray)
  RETURNS bool LANGUAGE sql IMMUTABLE AS
 'SELECT array_position(, NULL) IS NOT NULL';

utilisant un polymorphique type d'entrée cela fonctionne pour n'importe quel type de Tableau, pas seulement int[] .

Faire IMMUTABLE afin de permettre l'optimisation de la performance et d'expressions d'index.

• N'support de PostgreSQL "accent insensible" classements?

mais ne le faites pas STRICT , ce qui désactiverait la" fonction d'inclinaison " et nuirait aux performances.

• la fonction exécute plus rapidement sans modificateur STRICT?

si vous devez attraper le cas ar IS NULL , au lieu de faire la fonction STRICT , utilisez:

CREATE OR REPLACE FUNCTION f_array_has_null (anyarray)
  RETURNS bool LANGUAGE sql IMMUTABLE AS
 'SELECT CASE WHEN  IS NOT NULL
              THEN array_position(, NULL) IS NOT NULL END';

Pour Postgres 9.1 utilisez l'expression t_91 ci-dessus. Le reste s'applique inchangé.

---

question étroitement liée:

• Comment déterminer si NULL est contenu dans un tableau dans Postgres?