Andrew Dunstan <andrew@xxxxxxxxxxxx> writes: > On 12/12/2014 04:44 PM, Tom Lane wrote: >> Well, it'd be easy to fix if we were willing to invent distinct operators >> depending on which type you wanted out (perhaps ->> for text output as >> today, add ->># for numeric output, etc). > That was my immediate reaction. Not sure about the operator name. I'd > tentatively suggest -># (taking an int or text argument) and #># taking > a text[] argument, both returning numeric, and erroring out if the value > is a string, boolean, object or array. >> The usability issue could be fixed by teaching the planner to fold a >> construct like (jsonb ->> 'foo')::numeric into (jsonb ->># 'foo'). >> But I'm not sure how we do that except in a really ugly and ad-hoc >> fashion. > I would be inclined to add the operator and see how cumbersome people > find it. I suspect in many cases it might be sufficient. We can't just add the operator and worry about usability later; if we're thinking we might want to introduce such an automatic transformation, we have to be sure the new operator is defined in a way that allows the transformation to not change any semantics. What that means in this case is that if (jsonb ->> 'foo')::numeric would have succeeded, (jsonb ->># 'foo') has to succeed; which means it'd better be willing to attempt conversion of string values to numeric, not just throw an error on sight. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
