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

On Fri, Aug 1, 2014 at 12:24 AM, <dgrelaud(at)ideolys(dot)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