为什么 null||null 返回空值，而 concat(null,null) 在 postgres 中返回空字符串? [英] Why does null||null return a null value but concat(null,null) returns an empty string in postgres?

问题描述

我试图理解 concat() 和 || 之间的区别在 postgres 中使用空值时.如果 concat 两边都为空，为什么 concat() 返回一个空字符串?

以这个查询为例:

SELECT concat(NULL,NULL) AS null_concat, NULL||NULL AS null_pipes,concat(NULL,NULL) IS NULL is_concat_null, NULL||NULL IS NULL is_pipe_null

将返回:

我知道 concat() 会忽略空值，但是如果 concat 中的所有值都是空值，那么预期结果不是空值吗?这是 postgres 中所有函数的典型行为吗?我在有关此场景的文档中找不到任何内容.

我认为这可能是任何字符串函数的预期结果，但似乎并非如此.如果传递空值，upper() 和 left() 都返回空值:

SELECT concat(NULL), NULL||NULL, UPPER(null), left(NULL,1)

结果:

解决方案

在 concat() 函数中:

text concat(str "any",...) 连接所有参数.NULL 参数被忽略.

注意:NULL 参数被忽略.

想象一下:

输入参数 concat() 是动态的.

所以当我们写:concat('a',null,null,null,null) => 我们写的是:concat('a')

(相对于 || 操作符，NULL 破坏了一切)

<小时>

在 ||运营商:

<块引用>

字符串连接运算符 (||) 仍然接受非字符串输入，只要至少一个输入是字符串类型

所以 NULL||NULL 有错误的语法

但为什么不给错误呢?因为在concat操作中，如果我们不拒绝NULL(就像concat函数一样)，它们会压倒一切

SELECT NULL ||'aaa'||'bbb'||'ccc'||'ddd'

输出:

NULL

更多信息:

<块引用>

注意:在 PostgreSQL 8.3 之前，这些函数会默默接受由于存在，还有几种非字符串数据类型的值从这些数据类型到文本的隐式强制转换.那些胁迫已被删除，因为它们经常引起令人惊讶的行为.但是，字符串连接运算符 (||) 仍然接受非字符串输入，只要至少一个输入是字符串类型，如表9-6所示.对于其他情况，插入显式强制转换如果您需要复制之前的行为，请输入文本.

I'm trying to understand the differences between concat() and || in postgres when using nulls. Why does concat() return an empty string if both sides of the concat are null?

Take this query For Example:

SELECT concat(NULL,NULL) AS null_concat, NULL||NULL AS null_pipes, 
concat(NULL,NULL) IS NULL is_concat_null, NULL||NULL IS NULL is_pipe_null

will return:

I understand that concat() ignores nulls but if all the values in the concat are null, wouldn't the expected result be a null? Is this typical behavior of all functions in postgres? I couldn't find anything in the documentaion around this scenario.

Edit:

I had a thought that maybe this was the expected result of any string function but that does not appear to be the case. Both upper() and left() return nulls if a null value is passed:

SELECT concat(NULL), NULL||NULL, UPPER(null), left(NULL,1)

Result:

解决方案

in concat() function:

text concat(str "any",...)  Concatenate all arguments. NULL arguments are ignored.

Note: NULL arguments are ignored.

Imagine this:

The input arguments concat() are dynamical.

So when we write: concat('a',null,null,null,null) => we have written: concat('a')

(As opposed to the || operator that NULL destroyed everything)

---

in || operator:

the string concatenation operator (||) still accepts non-string input, so long as at least one input is of a string type

So NULL||NULL has a wrong syntax

But why not give Error? Because in the concat operation, if we do not reject the NULL (Like the concat function), they will overwhelm everything

SELECT NULL ||'aaa'||'bbb'||'ccc'||'ddd'

output:

NULL

more info:

Note: Before PostgreSQL 8.3, these functions would silently accept values of several non-string data types as well, due to the presence of implicit coercions from those data types to text. Those coercions have been removed because they frequently caused surprising behaviors. However, the string concatenation operator (||) still accepts non-string input, so long as at least one input is of a string type, as shown in Table 9-6. For other cases, insert an explicit coercion to text if you need to duplicate the previous behavior.

这篇关于为什么 null||null 返回空值，而 concat(null,null) 在 postgres 中返回空字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！