Re: jsonb_set() strictness considered harmful to data

On 2019-10-20 13:20:23 -0700, Steven Pousty wrote:
> I would think though that raising an exception is better than a default
> behavior which deletes data.
> As an app dev I am quite used to all sorts of "APIs" throwing exceptions and
> have learned to deal with them.
>
> This is my way of saying that raising an exception is an improvement over the
> current situation. May not be the "best" solution but definitely an
> improvement.

I somewhat disagree. SQL isn't in general a language which uses
exceptions a lot. It does have the value NULL to mean "unknown", and
generally unknown combined with something else results in an unknown
value again:

% psql wds
Null display is "(∅)".
Line style is unicode.
Border style is 2.
Unicode border line style is "double".
Timing is on.
Expanded display is used automatically.
psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
Type "help" for help.

wds=> select 4 + NULL;
╔══════════╗
║ ?column? ║
╟──────────╢
║ (∅) ║
╚══════════╝
(1 row)

Time: 0.924 ms
wds=> select replace('steven', 'e', NULL);
╔═════════╗
║ replace ║
╟─────────╢
║ (∅) ║
╚═════════╝
(1 row)

Time: 0.918 ms

Throwing an exception for a pure function seems "un-SQLy" to me. In
particular, jsonb_set does something similar for json values as replace
does for strings, so it should behave similarly.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>