Replace NULL values in an array in PostgreSQL

Question

SELECT ARRAY[1,2,3] - ARRAY[5,NULL,6]

I am using contrib _int.sql package for array operations in postgresql 8.4 In the above query there is a NULL in right hand side array. Because of this NULL value, it throws an error:

"ERROR:  array must not contain nulls"

Can anyone help me to remove the null values from the array?

Answer 1

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.