Re: BUG #11103: to_json() does not convert correctly DOMAINs type since 9.3.5 (int expected instead of string) ?

Thank you for your response.

> except that unsigned int and int do not have the same range of values
normally

Yes, of course, it was just for the example ;).

In fact, I showed you a basic test case to simplify the description of my
problem. But my real problem is a little bit more complex (custom domain
used in an array of custom types converted by array_to_json so it is not
easy to cast...).

But, you are right, now it is consistent with the documentation. And I have
noticed the code was a lot simplified in the commit of Tom Lane and it is
always better for maintenance... Sometimes, it is better to have a simple
code than doing to much "magic" for end users.

Ok, I will find other solutions (create a new cast function or change a
little bit my model).

Kind regards,

*David Grelaud*




2014-08-01 9:01 GMT+02:00 Michael Paquier <michael.paquier@gmail.com>:

> On Fri, Aug 1, 2014 at 12:24 AM,  <dgrelaud@ideolys.com> wrote:
> > With 9.3.5,  it returns a JSON string : '2' (with quotes)
> > With 9.3.4,  it returns a JSON int : 2 (without quotes)
>
> Double quotes actually, to make it valid JSON:
> =# CREATE DOMAIN UNSIGNED_INT4 AS INT4 CHECK (VALUE >= 0);
> CREATE DOMAIN
> =# SELECT to_json(2::UNSIGNED_INT4);
>  to_json
> ---------
>  "2"
> (1 row)
> You could still recast it back to int4 to enforce the constraint
> check, except that unsigned int and int do not have the same range of
> values normally (smth that your domain breaks as it cannot take values
> higher than 2^31 btw):
> =# SELECT to_json(2::UNSIGNED_INT4::int4);
>  to_json
> ---------
>  2
> (1 row)
>
> > If we do not use domains, SELECT to_json(2::INT4) returns always a JSON
> int
> > (even with 9.3.5).
> >
> > Is it related to the change made by Tom Lane?
> > commit 0ca6bda8e7501947c05f30c127f6d12ff90b5a64 and the release note
> 9.3.5
> > "Fix identification of input type category in to_json() and friends (Tom
> > Lane)"?
> To be picky, this commit is on 9.4 stable branch, on 9.3 it is 13c6799
> :) And yes the commit you are referring to is the origin of this
> modification of behavior.
>
> > Is it volontary? If so, don't worry, I will find a workaround.
>
> By looking at the documentation here about json functions
> (http://www.postgresql.org/docs/current/static/functions-json.html)
> and looking at to_json, there is the following quote:
> "If the data type is not built in, and there is a cast from the type
> to json, the cast function will be used to perform the conversion.
> Otherwise, for any value other than a number, a Boolean, or a null
> value, the text representation will be used."
> So in your case as there is no cast function to json for
> unsigned_int4, text representation is used. New behavior seems more
> consistent with the documentation.
>
> Regards,
> --
> Michael
>