Re: jsonb_set() strictness considered harmful to data

> On Jan 17, 2020, at 4:28 PM, Ariadne Conill wrote: > > Hello, > > January 17, 2020 5:21 PM, "Tomas Vondra" > wrote: > > Thank you very much for coming together and finding a solution to this bug! > > Ariadne Let’s leave it at “issue” :)

Re: jsonb_set() strictness considered harmful to data

Hello, January 17, 2020 5:21 PM, "Tomas Vondra" wrote: > On Wed, Jan 08, 2020 at 05:24:05PM +1030, Andrew Dunstan wrote: > >> On Wed, Jan 8, 2020 at 7:08 AM Pavel Stehule wrote: >>> Hi >>> >>> po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan >>> napsal: >> >> Updated version including docco

Re: jsonb_set() strictness considered harmful to data

On Wed, Jan 08, 2020 at 05:24:05PM +1030, Andrew Dunstan wrote: On Wed, Jan 8, 2020 at 7:08 AM Pavel Stehule wrote: Hi po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan napsal: Updated version including docco and better error message. cheers andrew I think so my objections are solved.

Re: jsonb_set() strictness considered harmful to data

On Wed, Jan 8, 2020 at 7:08 AM Pavel Stehule wrote: > > Hi > > po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan > napsal: >> >> >> Updated version including docco and better error message. >> >> cheers >> >> andrew > > > I think so my objections are solved. I have small objection > > + errdetail(

Re: jsonb_set() strictness considered harmful to data

Hi po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan < andrew.duns...@2ndquadrant.com> napsal: > On Thu, Nov 28, 2019 at 2:15 PM Andrew Dunstan > wrote: > > > > > > On 11/27/19 9:35 PM, Michael Paquier wrote: > > > On Fri, Nov 15, 2019 at 09:45:59PM +0100, Pavel Stehule wrote: > > >> Maybe ERRCODE

Re: jsonb_set() strictness considered harmful to data

On Thu, Nov 28, 2019 at 2:15 PM Andrew Dunstan wrote: > > > On 11/27/19 9:35 PM, Michael Paquier wrote: > > On Fri, Nov 15, 2019 at 09:45:59PM +0100, Pavel Stehule wrote: > >> Maybe ERRCODE_NULL_VALUE_NOT_ALLOWED, and "NULL is not allowed", > >> errdetail - a exception due setting "null_value_trea

Re: jsonb_set() strictness considered harmful to data

On 11/27/19 9:35 PM, Michael Paquier wrote: > On Fri, Nov 15, 2019 at 09:45:59PM +0100, Pavel Stehule wrote: >> Maybe ERRCODE_NULL_VALUE_NOT_ALLOWED, and "NULL is not allowed", >> errdetail - a exception due setting "null_value_treatment" => >> raise_exception >> and maybe some errhint - "Maybe y

Re: jsonb_set() strictness considered harmful to data

On Fri, Nov 15, 2019 at 09:45:59PM +0100, Pavel Stehule wrote: > Maybe ERRCODE_NULL_VALUE_NOT_ALLOWED, and "NULL is not allowed", > errdetail - a exception due setting "null_value_treatment" => > raise_exception > and maybe some errhint - "Maybe you would to use Jsonb NULL - "null"::jsonb" > > I d

Re: jsonb_set() strictness considered harmful to data

pá 15. 11. 2019 v 21:01 odesílatel Andrew Dunstan < andrew.duns...@2ndquadrant.com> napsal: > > On 11/15/19 2:14 PM, Pavel Stehule wrote: > > Hi > > > > > > > > For release 13+, I have given some more thought to what should be > > done. > > I think the bar for altering the behaviour of

Re: jsonb_set() strictness considered harmful to data

On 11/15/19 2:14 PM, Pavel Stehule wrote: > Hi > > > > For release 13+, I have given some more thought to what should be > done. > I think the bar for altering the behaviour of a function should be > rather higher than we have in the present case, and the longer the > function

Re: jsonb_set() strictness considered harmful to data

Hi > For release 13+, I have given some more thought to what should be done. > I think the bar for altering the behaviour of a function should be > rather higher than we have in the present case, and the longer the > function has been sanctioned by time the higher the bar should be. > However, I

Re: jsonb_set() strictness considered harmful to data

On Mon, Oct 28, 2019, at 08:52, Andrew Dunstan wrote: > > For release 13+, I have given some more thought to what should be done. > I think the bar for altering the behaviour of a function should be > rather higher than we have in the present case, and the longer the > function has been sanctio

Re: jsonb_set() strictness considered harmful to data

On 10/21/19 9:28 AM, Andrew Dunstan wrote: > On 10/21/19 2:07 AM, Tomas Vondra wrote: >> On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: I think the general premise of this thread is that the application developer does not realize that may be necessary, because it's a bit

Re: jsonb_set() strictness considered harmful to data

On 10/24/19 2:17 PM, Tom Lane wrote: Laurenz Albe writes: On Wed, 2019-10-23 at 13:00 -0600, Stuart McGraw wrote: It is less sensible with compound values where the rule can apply to individual scalar components. I agree that JSON can sensibly be viewed as a composite value, but ... And

Re: jsonb_set() strictness considered harmful to data

Laurenz Albe writes: > On Wed, 2019-10-23 at 13:00 -0600, Stuart McGraw wrote: >> It is less sensible with compound values where the rule can apply to >> individual scalar components. I agree that JSON can sensibly be viewed as a composite value, but ... >> And indeed that is what Postgresql do

Re: jsonb_set() strictness considered harmful to data

On Wed, 2019-10-23 at 13:00 -0600, Stuart McGraw wrote: > > You can only say that if you don't understand NULL (you wouldn't be alone). > > If I modify a JSON with an unknown value, the result is unknown. > > This seems very intuitive to me. > > Would you expect modifying an array value with an un

Re: jsonb_set() strictness considered harmful to data

On Wed, Oct 23, 2019 at 12:01 PM Stuart McGraw wrote: > When examples are given, they typically are with scalar values where > such behavior makes sense: the resulting scalar value has to be NULL > or non-NULL, it can't be both. > > It is less sensible with compound values where the rule can appl

Re: jsonb_set() strictness considered harmful to data

Hello, On Wed, 2019-10-23 at 20:33 +0200, Peter J. Holzer wrote: > > I grant that SQL NULL takes a bit to get used to. However, it is a > core > part of the SQL language and everyone who uses SQL must understand it > (I > don't remember when I first stumbled across "select * from t where c > = >

Re: jsonb_set() strictness considered harmful to data

On Wed, Oct 23, 2019 at 12:01 PM Stuart McGraw wrote: > Why the inconsistency between the array > type and json type? Are there any cases other than json where the entire > compound value is set to NULL as a result of one of its components being > NULL? That's a great point. It does look like hs

Re: jsonb_set() strictness considered harmful to data

On 10/23/19 5:42 AM, Laurenz Albe wrote: David G. Johnston wrote: Now if only the vast majority of users could have and keep this level of understanding in mind while writing complex queries so that they remember to always add protections to compensate for the unique design decision that SQL h

Re: jsonb_set() strictness considered harmful to data

On 2019-10-22 18:06:39 -0700, David G. Johnston wrote: > On Tue, Oct 22, 2019 at 3:55 PM Peter J. Holzer wrote: > 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 a

Re: jsonb_set() strictness considered harmful to data

On Wed, Oct 23, 2019 at 4:42 AM Laurenz Albe wrote: > David G. Johnston wrote: > > Now if only the vast majority of users could have and keep this level of > understanding > > in mind while writing complex queries so that they remember to always > add protections > > to compensate for the unique

Re: jsonb_set() strictness considered harmful to data

David G. Johnston wrote: > Now if only the vast majority of users could have and keep this level of > understanding > in mind while writing complex queries so that they remember to always add > protections > to compensate for the unique design decision that SQL has taken here... You can only say

Re: jsonb_set() strictness considered harmful to data

On Tue, Oct 22, 2019 at 3:55 PM Peter J. Holzer wrote: > 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 > a

Re: jsonb_set() strictness considered harmful to data

On 2019-10-22 09:16:05 +1100, raf wrote: > Steven Pousty wrote: > > In a perfect world I would agree with you. But often users do not read ALL > > the documentation before they use the function in their code OR they are > > not sure that the condition applies to them (until it does). > > I'm well

Re: jsonb_set() strictness considered harmful to data

On 2019-10-21 09:39:13 -0700, Steven Pousty wrote: > Turning a JSON null into a SQL null  and thereby "deleting" the data > is not the path of least surprises. But it doesn't do that: A JSON null is perfectly fine: wds=> select jsonb_set('{"a": 1, "b": 2}'::jsonb, '{c}', 'null'::jsonb); ╔

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

Re: jsonb_set() strictness considered harmful to data

Steven Pousty wrote: > On Sun, Oct 20, 2019 at 4:31 PM raf wrote: > > > Steven Pousty wrote: > > > > > I would think though that raising an exception is better than a > > > default behavior which deletes data. > > > > I can't help but feel the need to make the point that > > the function is not

Re: jsonb_set() strictness considered harmful to data

On 10/21/19 12:50 PM, Tomas Vondra wrote: On Mon, Oct 21, 2019 at 08:06:46AM -0700, Adrian Klaver wrote: On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in

Re: jsonb_set() strictness considered harmful to data

On Mon, Oct 21, 2019 at 08:06:46AM -0700, Adrian Klaver wrote: On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in stored procedures (because we can't do sub

Re: jsonb_set() strictness considered harmful to data

On 21/10/2019 17:39, Steven Pousty wrote:  Turning a JSON null into a SQL null  and thereby "deleting" the data is not the path of least surprises. In what situation does that happen? (If it's already been mentioned I missed it, long thread, sorry). Cheers,   Steve

Re: jsonb_set() strictness considered harmful to data

On Sun, Oct 20, 2019 at 4:31 PM raf wrote: > Steven Pousty wrote: > > > I would think though that raising an exception is better than a > > default behavior which deletes data. > > I can't help but feel the need to make the point that > the function is not deleting anything. It is just > returnin

Re: jsonb_set() strictness considered harmful to data

On Sun, Oct 20, 2019 at 3:51 PM Andrew Dunstan < andrew.duns...@2ndquadrant.com> wrote: > I'm not arguing against the idea of improving the situation. But I am > arguing against a minimal fix that will not provide much of value to a > careful app developer. i.e. I want to do more to support app de

Re: jsonb_set() strictness considered harmful to data

On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in stored procedures (because we can't do subtransactions, so no exception blocks). Can you explain the ab

Re: jsonb_set() strictness considered harmful to data

On 10/21/19 2:07 AM, Tomas Vondra wrote: > On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: >> >>> I think the general premise of this thread is that the application >>> developer does not realize that may be necessary, because it's a bit >>> surprising behavior, particularly when

Re: jsonb_set() strictness considered harmful to data

On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: On 10/20/19 4:18 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 03:48:05PM -0400, Andrew Dunstan wrote: On 10/20/19 1:14 PM, David G. Johnston wrote: On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan mailto:andrew.duns...@2ndquadr

Re: jsonb_set() strictness considered harmful to data

> > >> I would argue that only if the target parameter (the actual json value) > is NULL should the result be NULL. The function is documented as returning > the target, with modifications to a small part of its structure as > specified by the other parameters. It is strange for the result to sudde

Re: jsonb_set() strictness considered harmful to data

Hello, On Sun, 2019-10-20 at 18:51 -0400, Andrew Dunstan wrote: > On 10/20/19 4:18 PM, Tomas Vondra wrote: > > > >https://www.postgresql.org/docs/12/functions-json.html > > > > but that says nothing about how jsonb_set works with NULL values :- > > ( > > > We should certainly fix that. I a

Re: jsonb_set() strictness considered harmful to data

Steven Pousty wrote: > I would think though that raising an exception is better than a > default behavior which deletes data. I can't help but feel the need to make the point that the function is not deleting anything. It is just returning null. The deletion of data is being performed by an updat

Re: jsonb_set() strictness considered harmful to data

On 10/20/19 4:18 PM, Tomas Vondra wrote: > On Sun, Oct 20, 2019 at 03:48:05PM -0400, Andrew Dunstan wrote: >> >> On 10/20/19 1:14 PM, David G. Johnston wrote: >>> On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan >>> >> > wrote: >>> >>>     And yet another is t

Re: jsonb_set() strictness considered harmful to data

> That said, I think it is reasonable that a PostgreSQL JSON function > behaves in the way that JSON users would expect, so here is my +1 for > interpreting an SQL NULL as a JSON null in the above case Just to chime in as another application developer: the current functionality does seem pretty su

Re: jsonb_set() strictness considered harmful to data

On Fri, 2019-10-18 at 21:18 -0500, Ariadne Conill wrote: > postgres=# \pset null '(null)' > Null display is "(null)". > postgres=# select jsonb_set('{"a":1,"b":2,"c":3}'::jsonb, '{a}', NULL); > jsonb_set > --- > (null) > (1 row) > > This behaviour is basically giving an application develop

Re: jsonb_set() strictness considered harmful to data

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 si

Re: jsonb_set() strictness considered harmful to data

On Sun, Oct 20, 2019 at 03:48:05PM -0400, Andrew Dunstan wrote: On 10/20/19 1:14 PM, David G. Johnston wrote: On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan mailto:andrew.duns...@2ndquadrant.com>> wrote: And yet another is to raise an exception, which is easy to write but really punts

Re: jsonb_set() strictness considered harmful to data

On 10/20/19 1:14 PM, David G. Johnston wrote: > On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan > > wrote: > > And yet another is to > raise an exception, which is easy to write but really punts the issue > back to the application programmer who

Re: jsonb_set() strictness considered harmful to data

On Sun, Oct 20, 2019 at 5:31 AM Andrew Dunstan < andrew.duns...@2ndquadrant.com> wrote: > And yet another is to > raise an exception, which is easy to write but really punts the issue > back to the application programmer who will have to decide how to ensure > they never pass in a NULL parameter.

Re: jsonb_set() strictness considered harmful to data

On Sun, 20 Oct 2019 at 08:32, Andrew Dunstan wrote: > > Understood. I think the real question here is what it should do instead > when the value is NULL. Your behaviour above is one suggestion, which I > personally find intuitive. Another has been to remove the associated > key. Another is to ret

Re: jsonb_set() strictness considered harmful to data

On 10/20/19 4:39 AM, Floris Van Nee wrote: > > FWIW I've been bitten by this 'feature' more than once as well, > accidentally erasing a column. Now I usually write js = jsonb_set(js, > coalesce(new_column, 'null'::jsonb)) to prevent erasing the whole > column, and instead setting the value to a j

Re: jsonb_set() strictness considered harmful to data

On 19/10/2019 07:52, Ariadne Conill wrote: I would say that any thing like update whatever set column=jsonb_set(column, '{foo}', NULL) should throw an exception. It should do, literally, *anything* else but blank that column. steve=# create table foo (bar jsonb not null); CREATE TABLE ste

jsonb_set() strictness considered harmful to data

FWIW I've been bitten by this 'feature' more than once as well, accidentally erasing a column. Now I usually write js = jsonb_set(js, coalesce(new_column, 'null'::jsonb)) to prevent erasing the whole column, and instead setting the value to a jsonb null value, but I also found the STRICT behavio

Re: jsonb_set() strictness considered harmful to data

Hello, On Sat, Oct 19, 2019, 3:27 PM Tomas Vondra wrote: > On Sat, Oct 19, 2019 at 12:47:39PM -0400, Andrew Dunstan wrote: > > > >On 10/19/19 12:32 PM, David G. Johnston wrote: > >> On Sat, Oct 19, 2019 at 9:19 AM Tomas Vondra > >> mailto:tomas.von...@2ndquadrant.com>> > >> wrote: > >> > >>

Re: jsonb_set() strictness considered harmful to data

On Sat, Oct 19, 2019 at 12:47:39PM -0400, Andrew Dunstan wrote: On 10/19/19 12:32 PM, David G. Johnston wrote: On Sat, Oct 19, 2019 at 9:19 AM Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: > >We invented jsonb_set() (credit to Dmitry Dolgov). And we've had it >since 9.5

Re: jsonb_set() strictness considered harmful to data

On 10/18/19 7:18 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 7:04 PM Adrian Klaver wrote: On 10/18/19 4:31 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver wrote: On 10/18/19 3:11 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 5:01 P

Re: jsonb_set() strictness considered harmful to data

On 10/19/19 12:32 PM, David G. Johnston wrote: > On Sat, Oct 19, 2019 at 9:19 AM Tomas Vondra > mailto:tomas.von...@2ndquadrant.com>> > wrote: > > > > >We invented jsonb_set() (credit to Dmitry Dolgov). And we've had it > >since 9.5. That's five releases ago.  So it's a bit late to be

Re: jsonb_set() strictness considered harmful to data

On Sat, Oct 19, 2019 at 9:19 AM Tomas Vondra wrote: > > > >We invented jsonb_set() (credit to Dmitry Dolgov). And we've had it > >since 9.5. That's five releases ago. So it's a bit late to be coming to > >us telling us it's not safe (according to your preconceptions of what it > >should be doing

Re: jsonb_set() strictness considered harmful to data

On 10/19/19 12:18 PM, Tomas Vondra wrote: > On Sat, Oct 19, 2019 at 11:26:50AM -0400, Andrew Dunstan wrote: > > Not sure, but that seems rather confusing to me, because it's mixing SQL > NULL and JSON null, i.e. it's not clear to me why > >    jsonb_set(..., "...", NULL) > > should do the same th

Re: jsonb_set() strictness considered harmful to data

On Sat, Oct 19, 2019 at 11:21:26AM -0400, Stephen Frost wrote: Greetings, * Dmitry Dolgov (9erthali...@gmail.com) wrote: If we want to change it, the question is where to stop? Essentially we have: update table set data = some_func(data, some_args_with_null); where some_func happened to b

Re: jsonb_set() strictness considered harmful to data

On Sat, Oct 19, 2019 at 11:26:50AM -0400, Andrew Dunstan wrote: ... The hyperbole here is misplaced. There is a difference between a bug and a POLA violation. This might be the latter, but it isn't the former. So please tone it down a bit. It's not the function that's unsafe, but the ill-inform

Re: jsonb_set() strictness considered harmful to data

On 10/18/19 3:10 PM, Mark Felder wrote: > > On Fri, Oct 18, 2019, at 12:37, Ariadne Conill wrote: >> Hello, >> >> I am one of the primary maintainers of Pleroma, a federated social >> networking application written in Elixir, which uses PostgreSQL in >> ways that may be considered outside the typ

Re: jsonb_set() strictness considered harmful to data

Greetings, * Dmitry Dolgov (9erthali...@gmail.com) wrote: > If we want to change it, the question is where to stop? Essentially we have: > > update table set data = some_func(data, some_args_with_null); > > where some_func happened to be jsonb_set, but could be any strict function. I don't

Re: jsonb_set() strictness considered harmful to data

## Ariadne Conill (aria...@dereferenced.org): > 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 do

Re: jsonb_set() strictness considered harmful to data

> On Sat, Oct 19, 2019 at 1:08 PM Tomas Vondra > wrote: > > >Here is how other implementations handle this case: > > > >MySQL/MariaDB: > > > >select json_set('{"a":1,"b":2,"c":3}', '$.a', NULL) results in: > > {"a":null,"b":2,"c":3} > > > >Microsoft SQL Server: > > > >select json_modify('{"a":1

Re: jsonb_set() strictness considered harmful to data

On Fri, Oct 18, 2019 at 09:14:09PM -0500, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 6:52 PM Stephen Frost wrote: Greetings, * Ariadne Conill (aria...@dereferenced.org) wrote: > On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver wrote: > > https://www.postgresql.org/docs/11/functions-

Re: jsonb_set() strictness considered harmful to data

Hello, On Sat, Oct 19, 2019 at 12:52 AM Pavel Stehule wrote: > > > > so 19. 10. 2019 v 7:41 odesílatel David G. Johnston > napsal: >> >> On Friday, October 18, 2019, Pavel Stehule wrote: >> >>> >>> Probably there will be some applications that needs NULL result in >>> situations when value wa

Re: jsonb_set() strictness considered harmful to data

so 19. 10. 2019 v 7:41 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Friday, October 18, 2019, Pavel Stehule > wrote: > > >> Probably there will be some applications that needs NULL result in >> situations when value was not changed or when input value has not expected >

Re: jsonb_set() strictness considered harmful to data

On Friday, October 18, 2019, Pavel Stehule wrote: > Probably there will be some applications that needs NULL result in > situations when value was not changed or when input value has not expected > format. Design using in Postgres allows later customization - you can > implement with COALESCE ve

Re: jsonb_set() strictness considered harmful to data

Hi > What I am talking about is that jsonb_set(..., ..., NULL) returns SQL NULL. > > postgres=# \pset null '(null)' > Null display is "(null)". > postgres=# select jsonb_set('{"a":1,"b":2,"c":3}'::jsonb, '{a}', NULL); > jsonb_set > --- > (null) > (1 row) > > This behaviour is basically gi

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 7:04 PM Adrian Klaver wrote: > > On 10/18/19 4:31 PM, Ariadne Conill wrote: > > Hello, > > > > On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver > > wrote: > >> > >> On 10/18/19 3:11 PM, Ariadne Conill wrote: > >>> Hello, > >>> > >>> On Fri, Oct 18, 2019 at 5:01 PM Da

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 6:52 PM Stephen Frost wrote: > > Greetings, > > * Ariadne Conill (aria...@dereferenced.org) wrote: > > On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver > > wrote: > > > https://www.postgresql.org/docs/11/functions-json.html > > > " The field/element/path extraction o

Re: jsonb_set() strictness considered harmful to data

On 10/18/19 4:31 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver wrote: On 10/18/19 3:11 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston wrote: On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder wrote: ## Ariadne

Re: jsonb_set() strictness considered harmful to data

Greetings, * Ariadne Conill (aria...@dereferenced.org) wrote: > On Fri, Oct 18, 2019 at 5:57 PM Christoph Moench-Tegeder > wrote: > > ## Ariadne Conill (aria...@dereferenced.org): > > > Why don't we fix the database engine to not eat data when the > > > jsonb_set() operation fails? > > > > It did

Re: jsonb_set() strictness considered harmful to data

Greetings, * Ariadne Conill (aria...@dereferenced.org) wrote: > On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver > wrote: > > https://www.postgresql.org/docs/11/functions-json.html > > " The field/element/path extraction operators return NULL, rather than > > failing, if the JSON input does not hav

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 5:57 PM Christoph Moench-Tegeder wrote: > > ## Ariadne Conill (aria...@dereferenced.org): > > > Why don't we fix the database engine to not eat data when the > > jsonb_set() operation fails? > > It didn't fail, it worked like SQL (you've been doing SQL for too > lon

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 6:01 PM Adrian Klaver wrote: > > On 10/18/19 3:11 PM, Ariadne Conill wrote: > > Hello, > > > > On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston > > wrote: > >> > >> On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder > >> wrote: > >>> > >>> ## Ariadne Conil

Re: jsonb_set() strictness considered harmful to data

On 10/18/19 3:11 PM, Ariadne Conill wrote: Hello, On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston wrote: On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder wrote: ## Ariadne Conill (aria...@dereferenced.org): update users set info=jsonb_set(info, '{bar}', info->'foo'); Typi

Re: jsonb_set() strictness considered harmful to data

## Ariadne Conill (aria...@dereferenced.org): > Why don't we fix the database engine to not eat data when the > jsonb_set() operation fails? It didn't fail, it worked like SQL (you've been doing SQL for too long when you get used to the NULL propagation, but that's still what SQL does - check "+"

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 5:01 PM David G. Johnston wrote: > > On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder > wrote: >> >> ## Ariadne Conill (aria...@dereferenced.org): >> >> >update users set info=jsonb_set(info, '{bar}', info->'foo'); >> > >> > Typically, this works nicel

Re: jsonb_set() strictness considered harmful to data

Hello, On Fri, Oct 18, 2019 at 4:50 PM Christoph Moench-Tegeder wrote: > > ## Ariadne Conill (aria...@dereferenced.org): > > >update users set info=jsonb_set(info, '{bar}', info->'foo'); > > > > Typically, this works nicely, except for cases where evaluating > > info->'foo' results in an SQL

Re: jsonb_set() strictness considered harmful to data

On Fri, Oct 18, 2019 at 2:50 PM Christoph Moench-Tegeder wrote: > ## Ariadne Conill (aria...@dereferenced.org): > > >update users set info=jsonb_set(info, '{bar}', info->'foo'); > > > > Typically, this works nicely, except for cases where evaluating > > info->'foo' results in an SQL null bein

Re: jsonb_set() strictness considered harmful to data

## Ariadne Conill (aria...@dereferenced.org): >update users set info=jsonb_set(info, '{bar}', info->'foo'); > > Typically, this works nicely, except for cases where evaluating > info->'foo' results in an SQL null being returned. When that happens, > jsonb_set() returns an SQL null, which the

Re: jsonb_set() strictness considered harmful to data

On Fri, Oct 18, 2019, at 12:37, Ariadne Conill wrote: > Hello, > > I am one of the primary maintainers of Pleroma, a federated social > networking application written in Elixir, which uses PostgreSQL in > ways that may be considered outside the typical usage scenarios for > PostgreSQL. > > Nam

jsonb_set() strictness considered harmful to data

Hello, I am one of the primary maintainers of Pleroma, a federated social networking application written in Elixir, which uses PostgreSQL in ways that may be considered outside the typical usage scenarios for PostgreSQL. Namely, we leverage JSONB heavily as a backing store for JSON-LD documents[1