From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Tim Smith <randomdev4+postgres(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: "JSON does not support infinite date values" |
Date: | 2015-02-26 14:45:35 |
Message-ID: | 54EF318F.3090305@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 02/26/2015 03:55 AM, Tim Smith wrote:
> Hi,
>
> As far as I'm aware, JSON has no data types as such, and so why is
> Postgres (9.4.1) attempting to impose its own nonsense constraints ?
> Surely it should just insert the word 'infinity' into the JSON output,
> just like it displays in a simple SQL query ?
It did:
test=> select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.6 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)
test=> select to_json(row('infinity'::date));
to_json
-------------------
{"f1":"infinity"}
(1 row)
seems to have changed:
test=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.1 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)
test=# select to_json(row('infinity'::date));
ERROR: date out of range
DETAIL: JSON does not support infinite date values.
so will have to wait until the question Andres posed is answered.
>
> create table app_test.foo(a text,b date,c date,d boolean);
>
> create view app_test.bar as select * from app_test.foo where
> b<=now()::date and c>=now()::date and d=true;
>
> insert into app_test.foo(a,b,c,d) values ('zzz','2014-12-31','2014-02-01',true);
> insert into app_test.foo(a,b,c,d) values ('zzz','2015-02-01','infinity',true);
>
>
> foobar=> select * from app_test.bar where a='zzz' order by c asc limit 1;
> a | b | c | d
> -----+------------+----------+---
> zzz | 2015-02-01 | infinity | t
> (1 row)
>
>
> CREATE FUNCTION app_test.foobar(p_a text) RETURNS json AS $$
> DECLARE
> v_row app_test.bar%ROWTYPE;
> v_json json;
> BEGIN
> select * into strict v_row from app_test.bar where a=p_a order by c asc limit 1;
> select row_to_json(v_row) into v_json;
> return v_json;
> END;
> $$ LANGUAGE plpgsql;
>
>
> foobar=> select app_test.foobar('zzz');
> ERROR: date out of range
> DETAIL: JSON does not support infinite date values.
> CONTEXT: SQL statement "select row_to_json(v_row)"
> PL/pgSQL function app_test.foobar(text) line 7 at SQL statement
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2015-02-26 14:55:44 | Re: "JSON does not support infinite date values" |
Previous Message | Neil Tiffin | 2015-02-26 13:39:56 | Re: Create Virtual Indexes on Postgres |