postgresql 字符串与 null 的连接似乎使整个字符串无效 - 这是 Postgres 中所需的行为吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34934762/
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
String concatenation with a null seems to nullify the entire string - is that desired behavior in Postgres?
提问by Reinsbrain
In Postgres:
在 Postgres 中：
select 'test' || null returns null
I was expecting it would otherwise return 'test'.
我原以为它会返回“测试”。
Is this desired behavior? Seems weird that string concatenation with a null value would nullify the entire string...
这是理想的行为吗？看起来很奇怪，字符串与空值的连接会使整个字符串无效......
Referring to pg docs: http://www.postgresql.org/docs/9.1/static/functions-string.html
参考 pg 文档：http: //www.postgresql.org/docs/9.1/static/functions-string.html
"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."
"注意：在 PostgreSQL 8.3 之前，由于存在从这些数据类型到文本的隐式强制转换，这些函数也会默默地接受几种非字符串数据类型的值。这些强制转换已被删除，因为它们经常引起令人惊讶的行为。然而, 字符串连接运算符(||) 仍然接受非字符串输入，只要至少有一个输入是字符串类型，如表9-6所示。对于其他情况，如果需要，请插入对文本的显式强制转换复制以前的行为。”
Given that, using their concat string function example:
鉴于此，使用他们的 concat 字符串函数示例：
concat(str "any" [, str "any" [, ...] ]) text Concatenate all arguments. NULL arguments are ignored. concat('abcde', 2, NULL, 22) >> abcde222
concat(str "any" [, str "any" [, ...] ]) text 连接所有参数。NULL 参数被忽略。concat('abcde', 2, NULL, 22) >> abcde222
Should I just get used to this behavior with '||' concatenation or is this something that should be fixed?
我应该用'||'来习惯这种行为吗？串联还是应该解决这个问题？
采纳答案by a_horse_with_no_name
It's not a bug and it's not "weird".
这不是错误，也不是“奇怪”。
The SQL standard requires any expression that involves nullyields null. This is not limited to string concatenation, it also applies to computations e.g.: 42 * nullreturns null.
SQL 标准要求任何涉及nullyields 的表达式null。这不仅限于字符串连接，它还适用于计算，例如：42 * null返回null。
This also applies to comparisons: 42 > nullyields null. So the comparison it's neither true nor false. Although in reality this has the effect of "false", but more because it's "not true", rather then false. But negating such an expression yields nullagain, not "true".
这也适用于比较：42 > nullyields null。所以比较它既不真实也不虚假。虽然实际上这有“假”的效果，但更多的是因为它“不真实”，而不是假的。但是否定这样的表达null再次产生，而不是“真实”。
Because nullis so special, the only way to check if something is null is to use the operator IS NULLor IS NOT NULL. x is nullyields either true or false, it never yields null, so expressions using the is nullor is not nulloperator neverreturn null - so this is an exception to my statement above (thanks Jonathan for pointing that out).
因为null太特殊了，检查某个东西是否为空的唯一方法是使用运算符IS NULLor IS NOT NULL。x is null产生真或假，它永远不会产生null，所以使用is nulloris not null运算符的表达式永远不会返回 null - 所以这是我上面声明的一个例外（感谢乔纳森指出这一点）。
Another - maybe surprising - fact about nullvalues is how they are handled by aggregate functions. While the expression4 + 5 + nullyields null, the sum()over those (column) values would yield 9, because aggregates ignorenullvalues.
另一个——也许令人惊讶的——关于null值的事实是它们如何被聚合函数处理。当表达式4 + 5 + null产生 null 时，sum()超过那些（列）值将产生 9，因为聚合忽略null值。
Given the following table:
鉴于下表：
col1
--------
1
2
3
null
sum(col1)will return 6, and avg(col1)will return 2(sum = 6, number of elements added: 3)
sum(col1)将返回 6，avg(col1)并将返回2（总和 = 6，添加的元素数：3）
回答by Yevgeniy Afanasyev
Quote from documentation
从文档中引用
Concatenate all arguments. NULL arguments are ignored.
连接所有参数。NULL 参数被忽略。
Example
例子
concat('abcde', 2, NULL, 22)
Returns
退货
abcde222
See more in Documentation
回答by MyBrainHurts
Yes, it is desired behavior.
是的，这是理想的行为。
Here is an example in which it is very useful. Let's say you have a persons table an in it the fields title, firstname, nameaffixand lastname. To build a full name you can simply do this.
这是一个非常有用的示例。比方说，你有一个人的表中的在它的领域title，firstname，nameaffix和lastname。要建立全名，您可以简单地执行此操作。
COALESCE(title || ' ', '') || firstname || COALESCE(' ' || nameaffix, '') || ' ' || lastname
Without that strange behavior that would be quite an afford to place the spaces at the right spots depending on whether a field is null or not. Assuming a mandatory first name and last name any combination of title and name affix is covered.
如果没有这种奇怪的行为，根据字段是否为空将空格放置在正确的位置将是相当负担得起的。假设必须使用名字和姓氏，则涵盖了标题和名称词缀的任何组合。
回答by DatabaseShouter
As the guy who taught me SQL said, "NULL means 'unknown' it doesn't mean 'zero', or 'empty string' it means 'unknown'".
正如教我 SQL 的那个人所说的那样，“NULL 意味着‘未知’，它并不意味着‘零’，或者‘空字符串’意味着‘未知’”。
That is the reason it is desired behavior, the result of combining something with an unknown value is an unknown.
这就是它是期望行为的原因，将某物与未知值组合的结果是未知的。
This is also worth considering when designing new columns and what being nullable means for them.
在设计新列以及可空对它们意味着什么时，这也值得考虑。
