Re: jsonb_array_elements issue

On Wed, Aug 17, 2016 at 12:56 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Mephysto <mephystoonhell(at)gmail(dot)com> writes:
>> In my previous email I forgot to say that I create a cast in my database as
>> I made with JSON. The cast is this:
>
>> CREATE CAST (text AS JSONB) WITHOUT FUNCTION AS IMPLICIT;
>
> Well, that's rather a critical bit of information :-(
>
>> If you create this cast and you launch my select without the explicit cast,
>> you should get the error. With this cast created I reported some other
>> issues, so at this point my question is: can I use this implicit cast or is
>> better to explicit it anytime is needed?
>
> This cast definition is completely broken; it's astonishing that you
> haven't had outright crashes, because text and jsonb do NOT have the
> same underlying representation, which is what would be required to
> make a cast WITHOUT FUNCTION work correctly. But this certainly
> explains 'unknown type of jsonb container' errors.
>
> You could fix that by providing a suitable casting function. I'm still
> dubious that making it an implicit cast is a good idea though. The
> trouble with implicit casts is that they tend to kick in when you were
> not expecting them to. Past experience has suggested a rule of thumb
> that implicit cross-type-category casts are best avoided, and I'd
> certainly call this a type category crossing.

Just to put an extra stone on that. json is represented on-disk as a
text blob, which is why you are not seeing problems with it. Still,
using directly casting with :: in your SQL queries would prove to be
more robust in the long term.
--
Michael