在PostgreSQL中替换数组中的NULL值

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

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:

我在postgresql 8.4中使用contrib _int.sql包进行数组操作。在上面的查询中,右侧数组中有一个NULL。由于此NULL值,它会引发错误:

"ERROR:  array must not contain nulls"

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

有人可以帮我从数组中删除空值吗?

1 个解决方案

#1

1) Arrays can contain NULL values in PostgreSQL 8.4+

1)数组在PostgreSQL 8.4+中可以包含NULL值

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

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

2)但是你不能在标准PostgreSQL 8.4中从另一个ARRAY中减去一个ARRAY。

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.

3)你可以在PostgreSQL 8.4中安装contrib包intarray。

4) But you cannot subtract arrays containing NULL values.

4)但是你不能减去包含NULL值的数组。

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

5)你也可以在Ruby中减去数组。请参阅手册或此处的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.

Postgres 9.3或更高版本具有任何数组的array_replace(anyarray,NULL,anyelement)。手册。

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():

PostgreSQL 8.4引入了unexst()对于旧版本,您可以使用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.

免责声明:两个版本都不适合多维数组。