Horton

Re: SQL/JSON in PostgreSQL

Sven R. Kunze <srkunze@mail.de>

View thread View on postgr.es
From: "Sven R. Kunze" <srkunze@mail.de>
To: Andres Freund <andres@anarazel.de>, Oleg Bartunov <obartunov@gmail.com>
Cc: David Steele <david@pgmasters.net>, Pavel Stehule <pavel.stehule@gmail.com>, Pgsql Hackers <pgsql-hackers@postgresql.org>, Nikita Glukhov <n.gluhov@postgrespro.ru>, Teodor Sigaev <teodor@postgrespro.ru>, Alexander Korotkov <a.korotkov@postgrespro.ru>, andrew Dunstan <andrew@dunslane.net>
Date: 2017-03-07T21:43:16Z
Lists: pgsql-hackers 
Hi,

about the datetime issue: as far as I know, JSON does not define a 
serialization format for dates and timestamps.

On the other hand, YAML (as a superset of JSON) already supports a 
language-independent date(time) serialization format 
(http://yaml.org/type/timestamp.html).

I haven't had a glance into the SQL/JSON standard yet and a quick search 
didn't reveal anything. However, reading your test case here 
https://github.com/postgrespro/sqljson/blob/5a8a241/src/test/regress/sql/sql_json.sql#L411 
it seems as if you intend to parse all strings in the form of 
"YYYY-MM-DD" as dates. This is problematic in case a string happens to 
look like this but is not intended to be a date.

Just for the sake of completeness: YAML solves this issue by omitting 
the quotation marks around the date string (just as JSON integers have 
no quotations marks around them).

Regards,
Sven

Commits

  1. SQL/JSON: support the IS JSON predicate

  2. SQL/JSON: add standard JSON constructor functions