Why does null||null return a null value but concat(null,null) returns an empty string in postgres? [duplicate]

Question

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:

Answer 1

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.