如何在Postgres中检查数组是否为空

Question

我有一个Postgres函数：

CREATE OR REPLACE FUNCTION get_stats(
    _start_date timestamp with time zone,
    _stop_date timestamp with time zone,
    id_clients integer[],
    OUT date timestamp with time zone,
    OUT profit,
    OUT cost
)
RETURNS SETOF record
LANGUAGE plpgsql
AS $$
DECLARE
    query varchar := '';
BEGIN
... -- lot of code
IF id_clients IS NOT NULL THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;
... -- other code
END;
$$;

如果我运行类似于以下的查询：

SELECT * FROM get_stats('2014-07-01 00:00:00Etc/GMT-3'
                      , '2014-08-06 23:59:59Etc/GMT-3', '{}');

生成的查询具有以下条件：

"... AND id = ANY('{}')..."

但是，如果一个数组是空的，这个条件不应该在查询中表示。
如何检查客户端数组不为空？

我也尝试了两种变体：

IF ARRAY_UPPER(id_clients) IS NOT NULL THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;

同时：

IF ARRAY_LENGTH(id_clients) THEN
    query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;

在这两种情况下，我都收到了这个错误： ARRAY_UPPER(ARRAY_LENGTH)不存在;

- joni jones

---

http://www.postgresql.org/message-id/[e-mail] - Gopesh Sharma

Answer 1

array_length() 需要两个参数，第二个参数是数组的维度。

array_length(id_clients, 1) > 0

所以：

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

这不包括空数组和NULL。

在Postgres 9.4或更高版本中使用cardinality()。请参阅@bronzenose添加的答案。

---

但是如果你正在将一个查询与EXECUTE连接起来运行，使用USING子句传递值会更明智。例如：

• 将多行子查询作为参数传递给`execute using`
• 如何在Postgres函数中使用EXECUTE FORMAT ... USING

---

要明确检查一个数组是否为空，就像你的标题所说的那样（但这不是你在这里需要的），只需将其与一个空数组进行比较即可。

id_clients = '{}'

这就是全部。你得到的是： true .. 数组为空
null .. 数组为NULL
false .. 其他情况（数组有元素 - 即使只有null元素）

- Erwin Brandstetter

---

谢谢@Erwin Brandstetter，我真的错过了array_length的第二个参数。 - joni jones

在Postgres 9.5上，对于空数组它会返回null。 - Marcin Król

@MarcinKról：这是设计上的，不是使用用例的问题：当表达式求值为“TRUE”而不是“FALSE”或“NULL”时，进入“IF”分支。 - Erwin Brandstetter

@ErwinBrandstetter 没错，你说得对，不过如果你能包含一个回答问题标题的版本会更好。 - Marcin Król

@MarcinKról：标题有点误导性。我添加了另一个答案来解决它。 - Erwin Brandstetter

Answer 2

如果由于某种原因您不想提供数组的维度，则cardinality将返回空数组的0值大小:

从文档中可以看到:

cardinality(anyarray)返回数组中元素的总数，如果数组为空则返回0

- bronzenose

---

在9.2版本及之前的版本中不存在。我猜它已经在9.4版本中添加了。 - user3526905

Answer 3

我在使用Spring Boot和PostgreSQL的项目中遇到了这个问题。

我有一个数组列表参数，它可以是null或空的，如果它是空的，你应该忽略它。

这个解决方案很简单。在仓库中检查列表是否为空，如果为空，则传递null，否则你将会遇到错误：

private static final String IDs = "Ids";
private static final String HAS_IDs = "hasIds";

...
parameters.addValue(IDs, CollectionUtils.isEmpty(params.getIds()) ? null : params.getIds());

创建一个布尔参数，用于判断列表是否为空：

parameters.addValue(HAS_IDs, !CollectionUtils.isEmpty(params.getIds()));

在 SQL 中使用这个：

select * from table_name where
:hasIds = false or id in (:Ids)

- eku-code

Answer 4

检查数组是否为空的一种方法是使用array_ndims函数，它返回数组的维度数。如果数组为空，它将返回0。我正在使用11版本的PostgreSQL。

$$
DECLARE

    IDS_PATROCINADOR_RED_0 INTEGER[] := ARRAY []::INTEGER[];
    IDS                        INTEGER;
BEGIN

    IF array_ndims(IDS_PATROCINADOR_RED_0) > 0 THEN
        IDS = array_length(ARRAY []::INTEGER, 1);
    ELSE
        IDS = 0 ;
    end if;
    RAISE NOTICE '%', IDS;
END

$$;

输出：

[2020-04-22 11:06:22] [00000] 0

[2020-04-22 11:06:22] 在 143 毫秒内完成

- zannier

Answer 5

以下示例接受数组值、NULL或空字符串作为查询参数。一些框架在您尝试将NULL作为查询参数传递并发送空字符串时会导致SQL错误。

查询使用json映射检查数组参数是否为空：

SELECT CAST(ARRAY_TO_JSON(ARRAY[:myArrayParameter]) AS VARCHAR) IN ('[null]', '[""]')

- Zon