Re: Desired behavior for || (jsonb_concat)

On Thu, Jul 6, 2017 at 3:25 PM, Seamus Abshere <seamus(at)abshere(dot)net> wrote:

> hi,
>
> # select '{"a":1}'::jsonb || null;
> ?column?
> ----------
> null
> (1 row)
>
> Is there a theoretical reason that this has to return null as opposed to
> just {"a":1}?
>
>
Most operators in SQL, when given a null as an operand, output null. Many
of the underlying functions likewise are defined to be STRICT. At this
point its fair to say its a long-standing convention.

Aggregation of a null has mixed results as to whether the null is processed
or ignored - the later typically in the math aggregations.

The following is a relatively easy work around for those who must handle
the possibility of nulls in their data.

​select '{"a":1}'::jsonb || COALESCE(null, '{}')::jsonb;
​
While most such expressions could be seen to have an obvious default
behavior when dealing with null SQL generally forces the user to make an
explicit declaration of intent and falls back to "unknown" if that is not
done.

David J.