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?
1) Arrays can contain NULL values in PostgreSQL 8.4+
2) But you cannot subtract one ARRAY from another in standard PostgreSQL 8.4.
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:
unnest()
was introduced with PostgreSQL 8.4For older versions you can use
generate_series()
:Call:
Disclaimer: both versions are not fit for multidimensional arrays.