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

Michael Paquier wrote
> On Fri, Aug 1, 2014 at 12:24 AM, <

> dgrelaud@

> > 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.

Since the unsigned_int4 is a domain now, in head, it would appear without
the quotes. Before the patch, in 9.3.4, it also appeared without the
quotes. After the patch, in 9.3.5, it now has quotes so the patch has
already changed user-visible behavior which is the reason Tom didn't
back-patch the part that pertained to domains.

The work-around should be to simply CREATE CAST from the domain to json.

Seems testing on other types should be done and, depending on the results,
the domain part of the commit could be back-patched as well.

Also, a comment on domains should be considered though if they do
pass-through to the base type that would be expected and thus superfluous to
the informed reader. If this new behavior remains in 9.3 it should probably
be documented.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11103-to-json-does-not-convert-correctly-DOMAINs-type-since-9-3-5-int-expected-instead-of-string-tp5813459p5813509.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.