Re: jsonb and nested hstore

On Feb 27, 2014, at 9:12 PM, Craig Ringer <craig(at)2ndQuadrant(dot)com> wrote:

> On 02/28/2014 12:43 PM, Christophe Pettus wrote:
>> My proposal is that we break the dependencies of jsonb (at least, at the user-visible level) on hstore2, thus allowing it in core successfully. jsonb || jsonb returning hstore seems like a bug to me, not a feature we should be supporting.
>
> Urgh, really?
>
> That's not something I'd be excited to be stuck with into the future.

The reason that we're even here is that there's no jsonb || jsonb operator (or the other operators that one would expect).

If you try || without the hstore, you get an error, of course:

postgres=# select '{"foo":{"bar":"yellow"}}'::jsonb || '{}'::jsonb;
ERROR: operator does not exist: jsonb || jsonb
LINE 1: select '{"foo":{"bar":"yellow"}}'::jsonb || '{}'::jsonb;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The reason it works with hstore installed is that there's an implicit cast from hstore to jsonb:

postgres=# create extension hstore;
CREATE EXTENSION
postgres=# select '{"foo":{"bar":"yellow"}}'::jsonb || '{}'::jsonb;
?column?
--------------------------
"foo"=>{"bar"=>"yellow"}
(1 row)

--

But I think we're piling broken on broken here. Just creating an appropriate jsonb || jsonb operator solves this problem. That seems the clear route forward.

--
-- Christophe Pettus
xof(at)thebuild(dot)com