Re: jsonb and nested hstore

On 02/28/2014 07:19 AM, Thom Brown wrote:
> On 28 February 2014 08:12, Andres Freund <andres@2ndquadrant.com 
> <mailto:andres@2ndquadrant.com>> wrote:
>
>     On 2014-02-27 15:06:33 -0500, Andrew Dunstan wrote:
>     > You realize that this API dates from 9.3 and has been used in
>     numerous
>     > extensions, right? So the names are pretty well fixed, for good
>     or ill.
>
>     Sure. Doesn't prevent adding a couple more comments tho. I've only
>     noticed this because I opened the header as a reference when reading
>     your patch. Anyway, do something based on that feedback or not, your
>     choice ;)
>
>
> Can I ask why I can do this:
>
> SELECT review %> 'product'->'title' as product_title
> FROM rating;
>
> But I can't do this:
>
> SELECT review->'product'->'title' as product_title
> FROM rating;
>
> ERROR:  operator does not exist: hstore -> hstore
> LINE 1: explain select review -> 'product'::hstore ->'title' as prod...
>
> Yet I can do this:
>
> SELECT review::json->'product'->'title' as product_title
> FROM rating;
>
>

I don't think this complaint has anything to do with the text you 
quoted, so you've kinda hijacked the thread slightly.

But anyway, I think we've seen enough of these to conclude that the 
casts from hstore to jsonb and back should not be implicit. I am fairly 
confident that changing that would fix your complaint and the similar 
one that Peter Geoghegan had.

cheers

andrew