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
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?
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 emptyNULL
.. array is NULLFALSE
.. any other case (array has elements - even if just NULL elements)
What's the proper way to test if a Postgresql array field is either null or empty
{}
seems to represent an empty array, which would explain both the value being NOT NULL
and ARRAY_LENGTH({})
not returning anything – though I'd expect it to return 0 on {}
, perhaps that's a PostgreSQL peculiarity with which I'm unfamiliar.
Is there a reason you can't just test the return value of ARRAY_LENGTH
, as e.g.
SELECT id FROM table WHERE ARRAY_LENGTH(black_list, 1) IS NULL OR ARRAY_LENGTH(black_list, 1) < 1
Assuming ARRAY_LENGTH()
doesn't lose its mind on empty values such as that for id=12
in the example above, it seems like that'd do the trick.
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
Stored procedure: how to check if NULL is present in postgresql array or not?
You can't use the ANY
operator to check for NULL values. You have to unnest the array and count the number of NULL elements:
if (select count(*) from unnest(user_zones_ids) as t(x) where x is null) > 0 then
... do something ...
end if;
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
Postgres - How to check for an empty array
The syntax should be:
SELECT
COUNT(*)
FROM
table
WHERE
datasets = '{}'
You use quotes plus curly braces to show array literals.
Postgres: check if array field contains value?
This should work:
select * from mytable where 'Journal'=ANY(pub_types);
i.e. the syntax is <value> = ANY ( <array> )
. Also notice that string literals in postresql are written with single quotes.
Related Topics
Oracle SQL to Sort Version Numbers
How to Relate 3 Tables Depending on Event
Why Can't I Reorder My SQL Server Columns
On Duplicate Key Update Feature in H2
How to Convert Cyrillic Stored as Latin1 ( SQL ) to True Utf8 Cyrillic with Iconv
Return Value at Max Date for a Particular Id
How to Change Default Systemdate from Ymd to Dmy
Merging Two Sqlite Databases Which Both Have Junction Tables
How to Get Current/Todays Date Data in SQL Server
Insert Large Amount of Data Efficiently with Sql
Update Multiple Rows Using Case When - Oracle
Why Is There a Scan on My Clustered Index
Group by Week, How to Get Empty Weeks
Create Geometry/Geography Field from Latitude & Longitude Fields (Sql Server)