SQL Postgres如何判断数组是否为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25155666/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if an array is empty in Postgres
提问by joni jones
I have a Postgres function:
我有一个 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;
$$;
So if I run query something like this:
所以如果我运行查询是这样的：
SELECT * FROM get_stats('2014-07-01 00:00:00Etc/GMT-3'
, '2014-08-06 23:59:59Etc/GMT-3', '{}');
Generated query has this condition:
生成的查询具有以下条件：
"... AND id = ANY('{}')..."
But if an array is empty this condition should not be represented in query.
How can I check if the array of clients is not empty?
但是，如果数组为空，则不应在查询中表示此条件。
如何检查客户端数组是否为空？
I've also tried two variants:
我还尝试了两种变体：
IF ARRAY_UPPER(id_clients) IS NOT NULL THEN
query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;
And:
和：
IF ARRAY_LENGTH(id_clients) THEN
query := query||' AND id = ANY ('||quote_nullable(id_clients)||')';
END IF;
In both cases I got this error: ARRAY_UPPER(ARRAY_LENGTH) doesn't exists;
在这两种情况下，我得到这个错误：ARRAY_UPPER(ARRAY_LENGTH) doesn't exists;
回答by Erwin Brandstetter
array_length()requires twoparameters, the second being the dimension of the array:
array_length()需要两个参数，第二个是数组的维度：
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 andNULL.
这不包括空数组和NULL。
Or use cardinality()in Postgres 9.4 or later. See added answer by @bronzenose.
或者cardinality()在 Postgres 9.4 或更高版本中使用。请参阅@bronzenose 添加的答案。
But if you're concatenating a query to run with EXECUTE, it would be smarter to pass values with a USINGclause. Examples:
但是，如果您要连接一个查询以与 一起运行EXECUTE，则使用USING子句传递值会更明智。例子：
- 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 notwhat 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)
TRUE.. 数组为空NULL.. 数组为 NULL FALSE.. 任何其他情况（数组有元素 - 即使只有 NULL 元素）
回答by bronzenose
if for some reason you don't want to supply the dimension of the array, cardinalitywill return 0 for an empty array:
如果由于某种原因您不想提供数组的维度，cardinality则将为空数组返回 0：
From the docs:
从文档：
cardinality(anyarray) returns the total number of elements in the array, or 0 if the array is empty
cardinality(anyarray) 返回数组中元素的总数，如果数组为空则返回 0
回答by zannier
One way to check if the array is empty would be with array_ndims which returns the number of dimensions of the array, if the array is empty it will return 0. I'm working with version 11 postgresql.
检查数组是否为空的一种方法是使用 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
$$;
$$;
output:
输出：
[2020-04-22 11:06:22] [00000] 0
[2020-04-22 11:06:22] completed in 143 ms
[2020-04-22 11:06:22] 143毫秒完成
回答by Zon
Below example accepts array values, NULLor empty stringas query parameter. Some frameworks result in an SQL error when you try to pass NULLas query parameter and you send an empty string instead.
下面的示例接受array values,NULL或empty string作为查询参数。当您尝试NULL作为查询参数传递并发送空字符串时，某些框架会导致 SQL 错误。
The query checks if array-parameter is empty using jsonmapping:
查询使用 json映射检查 array-parameter是否为空：
SELECT CAST(ARRAY_TO_JSON(ARRAY[:myArrayParameter]) AS VARCHAR) IN ('[null]', '[""]')
