Is Array All Nulls in Postgresql

Is array all NULLs in PostgreSQL

I think I got the shortest answer, while still preserving 4 = ALL (ARRAY[4,5]::integer[]); construct:

Live test: https://www.db-fiddle.com/f/6DuB1N4FdcvZdxKiHczu5y/1

select
y, true = ALL (select unnest(z) is null)
from x

How to determine if NULL is contained in an array in Postgres?

select exists (
    select 1 
    from unnest(array[1, null]) s(a)
    where a is null
);
 exists 
--------
 t

Or shorter:

select bool_or(a is null)
from unnest(array[1, null]) s(a)
;
 bool_or 
---------
 t

Do empty arrays equal NULL in PostgreSQL or is it an array with NULL inside?

You should use an empty array.

A NULL value means "unknown", so it won't work like expected with a condition like this:

WHERE NOT ARRAY[42] <@ followers

Here <@ is the "contains" operator, and the result will not be TRUE if followers IS NULL.

I also recommend to avoid storing NULL values as array elements. An array that contains NULL is different from an empty array, and again the semantics of NULL may lead to confusion.

Check if NULL exists in Postgres array

Postgres 9.5 or later

Or use array_position(). Basically:

SELECT array_position(arr, NULL) IS NOT NULL AS array_has_null

See demo below.

Postgres 9.3 or later

You can test with the built-in functions array_remove() or array_replace().

Postgres 9.1 or any version

If you know a single element that can never exist in your arrays, you can use this fast expression. Say, you have an array of positive numbers, and -1 can never be in it:

-1 = ANY(arr) IS NULL

Related answer with detailed explanation:

• Is array all NULLs in PostgreSQL

If you cannot be absolutely sure, you could fall back to one of the expensive but safe methods with unnest(). Like:

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

or:

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

But you can have fast and safe with a CASE expression. Use an unlikely number and fall back to the safe method if it should exist. You may want to treat the case arr IS NULL separately. See demo below.

Demo

SELECT num, arr, expect
     , -1 = ANY(arr) IS NULL                                    AS t_1   --  50 ms
     , (SELECT bool_or(x IS NULL) FROM unnest(arr) x)           AS t_2   -- 754 ms
     , EXISTS (SELECT 1 FROM unnest(arr) x WHERE x IS NULL)     AS t_3   -- 521 ms
     , CASE -1 = ANY(arr)
         WHEN FALSE THEN FALSE
         WHEN TRUE THEN EXISTS (SELECT 1 FROM unnest(arr) x WHERE x IS NULL)
         ELSE NULLIF(arr IS NOT NULL, FALSE)  -- catch arr IS NULL       --  55 ms
      -- ELSE TRUE  -- simpler for columns defined NOT NULL              --  51 ms
       END                                                      AS t_91
     , array_replace(arr, NULL, 0) <> arr                       AS t_93a --  99 ms
     , array_remove(arr, NULL) <> arr                           AS t_93b --  96 ms
     , cardinality(array_remove(arr, NULL)) <> cardinality(arr) AS t_94  --  81 ms
     , COALESCE(array_position(arr, NULL::int), 0) > 0          AS t_95a --  49 ms
     , array_position(arr, NULL) IS NOT NULL                    AS t_95b --  45 ms
     , CASE WHEN arr IS NOT NULL
            THEN array_position(arr, 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, arr, expect);

Result:

 num |  arr        | 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

Note that array_remove() and array_position() are not allowed for multi-dimensional arrays. All expressions to the right of t_93a only work for 1-dimenstioal arrays.

db<>fiddle here - Postgres 13, with more tests

_{Old sqlfiddle}

Benchmark setup

The added times are from a benchmark test with 200k rows in Postgres 9.5. This is my setup:

CREATE TABLE t AS
SELECT row_number() OVER() AS num
     , array_agg(elem) AS arr
     , 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;

Function wrapper

For repeated use, I would create a function in Postgres 9.5 like this:

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

PARALLEL SAFE only for Postgres 9.6 or later.

Using a polymorphic input type this works for any array type, not just int[].

Make it IMMUTABLE to allow performance optimization and index expressions.

• Does PostgreSQL support "accent insensitive" collations?

But don't make it STRICT, which would disable "function inlining" and impair performance because array_position() is not STRICT itself. See:

• Function executes faster without STRICT modifier?

If you need to catch the case arr IS NULL:

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

For Postgres 9.1 use the t_91 expression from above. The rest applies unchanged.

Closely related:

• How to determine if NULL is contained in an array in Postgres?

Postgres: array of NULL values

The engine can't infer the type of the array since all you have is NULLs there, and NULL can be of any type. So you'd somehow need to tell it it's a real NULL by casting it to real:

INSERT INTO test (id, depth_mm) VALUES (2, ARRAY[NULL::real,NULL,NULL]);

Or you can cast the entire array:

INSERT INTO test (id, depth_mm) VALUES (2, ARRAY[NULL,NULL,NULL]::real[]);

How is a null value/array handled in postgres?

In Postgres, all of your expressions are valid excepted for the last one: '{null,}', which would raise error:

malformed array literal: "{null,}"

It is also worth noting that there is a difference between null (and undefined value) and {} (an empty array). Say you want to write to a column has a not null constraint, null would fail while {} would be allowed.

Demo on DB Fiddle:

-- create the table
create table t (array_field int[])

-- insert values
insert into t values
    ('{1,2,3}'),
    ('{3,4,5}'),
    ('{null,2,3,4}'),
    (null),
    ('{}')
;
-- 5 rows affected

-- won't work
insert into t values ('{null,}');
-- ERROR:  malformed array literal: "{null,}"
-- LINE 1: insert into t values ('{null,}')
--                               ^
-- DETAIL:  Unexpected "}" character.

-- check the results
select array_field, array_length(array_field, 1) from t

array_field  | array_length
:----------- | -----------:
{1,2,3}      |            3
{3,4,5}      |            3
{NULL,2,3,4} |            4
null         |         null
{}           |         null

How to check if an array is empty in Postgres

array_length() requires two parameters, the second being the dimension of the array:

array_length(id_clients, 1) > 0

So:

IF array_length(id_clients, 1) > 0 THEN
    query := query || format(' AND id = ANY(%L))', id_clients);
END IF;

This excludes both empty array and NULL.

Or use cardinality() in Postgres 9.4 or later. See added answer by @bronzenose.

---

But if you're concatenating a query to run with EXECUTE, it would be smarter to pass values with a USING clause. Examples:

• Multirow subselect as parameter to `execute using`
• How to use EXECUTE FORMAT ... USING in postgres function

---

BTW, to explicitly check whether an array is empty (like your title says - but that's not what you need here) just compare it to an empty array:

id_clients = '{}'

That's all. You get:

TRUE .. array is empty

NULL .. array is NULL

FALSE .. any other case (array has elements - even if just NULL elements)

Postgres ignoring null values when using filter as not in array

NULL kinda means "unknown", so the expressions

NULL = NULL

and

NULL != NULL

are neither true nor false, they're NULL. Because it is not known whether an "unknown" value is equal or unequal to another "unknown" value.

Since <> ANY uses an equality test, if the value searched in the array is NULL, then the result will be NULL.

So your second query is correct.

Replace NULL values in an array in PostgreSQL

1) Arrays can contain NULL values in PostgreSQL 8.4+

db=# SELECT ARRAY[5,NULL,6];
   array
------------
 {5,NULL,6}

2) But you cannot subtract one ARRAY from another in standard PostgreSQL 8.4.

db=# SELECT ARRAY[1,2,3] - ARRAY[5,NULL,6];
ERROR:  operator does not exist: integer[] - integer[]

3) You can do that in PostgreSQL 8.4 with the contrib package intarray installed.

4) But you cannot subtract arrays containing NULL values.

5) You can also subtract arrays in Ruby. See here in the manual, or here on SO.

---

Solution to replace NULLs in an integer array in PostgreSQL:

Postgres 9.3 or later has array_replace(anyarray, NULL, anyelement) for any array. The manual.

In older versions:

CREATE OR REPLACE FUNCTION f_int_array_replace_null (int[], int)
RETURNS int[] AS
$$
SELECT ARRAY (
    SELECT COALESCE(x, $2)
    FROM   unnest($1) x);
$$ LANGUAGE SQL IMMUTABLE;

unnest() was introduced with PostgreSQL 8.4
For older versions you can use generate_series():

CREATE OR REPLACE FUNCTION f_int_array_replace_null (int[], int)
RETURNS int[] AS
$$
SELECT ARRAY (
    SELECT COALESCE($1[i], $2)
    FROM   generate_series(1, array_upper($1, 1)) x(i));
$$ LANGUAGE SQL IMMUTABLE; 

Call:

event=# SELECT f_int_array_replace_null (ARRAY[5,NULL,6], 0);
 f_int_array_replace_null
--------------------------
 {5,0,6}

Disclaimer: both versions are not fit for multidimensional arrays.

---

Related Topics