## Ariadne Conill (ariadne@xxxxxxxxxxxxxxxx): > NULL propagation makes sense in the context of traditional SQL. What > users expect from the JSONB support is for it to behave as JSON > manipulation behaves everywhere else. Well, some users expect that. Others are using this interface as it is documented and implemented right now. And that's what makes this a somewhat difficult case: I wouldn't argue for one behaviour or the other if this was new functionality. But jsonb_set() was added in 9.5, and changing that behaviour now will make other people about as unhappy as you are right now. Further, "now" is a rather flexible term: the function cannot be changed "right now" with the next bugfix release (may break existing applications, deterring people from installing bugfixes: very bad) and there's about no way to get a new function into a bugfix release (catversion bump). The next chance to do anything here is version 13, to be expected around this time next year. This gives us ample time to think about a solution which is consistent and works for (almost) everyone - no need to force a behaviour change in that function right now (and in case it comes to that: which other json/jsonb-functions would be affected?). That creates a kind of bind for your case: you cannot rely on the new behaviour until the new version is in reasonably widespread use. Database servers are long-lived beasts - in the field, version 8.4 has finally mostly disappeared this year, but we still get some questions about that version here on the lists (8.4 went EOL over five years ago). At some point, you'll need to make a cut and require your users to upgrade the database. > At some point, you have to start pondering whether the behaviour > does not make logical sense in the context that people frame the JSONB > type and it's associated manipulation functions. But it does make sense from a SQL point of view - and this is a SQL database. JSON is not SQL (the sheer amount of "Note" in between the JSON functions and operators documentation is proof of that) and nots ASN.1, "people expect" depends a lot on what kind of people you ask. None of these expectations is "right" or "wrong" in an absolute manner. Code has to be "absolute" in order to be deterministic, and it should do so in a way that is unsurprising to the least amount of users: I'm willing to concede that jsonb_set() fails this test, but I'm still not convinced that your approach is much better just because it fits your specific use case. > It is not *obvious* > that jsonb_set() will trash your data, but that is what it is capable > of doing. It didn't. The data still fit the constraints you put on it: none, unfortunately. Which leads me to the advice for the time being (until we have this sorted out in one way or another, possibly the next major release): at least put a NOT NULL on columns which must be not NULL - that alone would have gone a long way to prevent the issues you've unfortunately had. You could even put CHECK constraints on your JSONB (like "CHECK (j->'info' IS NOT NULL)") to make sure it stays well-formed. As a SQL person, I'd even argue that you shouldn't use JSON columns for key data - there is a certain mismatch between SQL and JSON, which will get you now and then, and once you've implemented all the checks to be safe, you've build a type system when the database would have given you one for free. (And running UPDATEs inside your JSONB fields is not as efficient as on simple columns). And finally, you might put some version information in your database schema, so the application can check if all the neccessary data migrations have been run. Regards, Christoph -- Spare Space
