Re: [GENERAL] Desired behavior for || (jsonb_concat)

[David G. Johnston]

On Thu, Jul 6, 2017 at 3:25 PM, Seamus Abshere  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.

[GENERAL] Desired behavior for || (jsonb_concat)

[Seamus Abshere]

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}?

Thanks,
Seamus

--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general