Horton
Menu

Re: SQL/JSON: functions

Andrew Dunstan <andrew@dunslane.net>

View thread
From: Andrew Dunstan <andrew@dunslane.net>
To: Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>, PostgreSQL-development <pgsql-hackers@postgresql.org>, Dmitry Dolgov <9erthalion6@gmail.com>, Oleg Bartunov <obartunov@postgrespro.ru>, Erik Rijkers <er@xs4all.nl>
Date: 2022-01-06T16:35:52Z
Lists: pgsql-hackers 
On 1/6/22 06:24, Himanshu Upadhyaya wrote:
> I have one general question on the below scenario.
> CREATE TABLE T (Id INTEGER PRIMARY KEY,Jcol CHARACTER VARYING ( 5000
> )CHECK ( Jcol IS JSON ) );
> insert into T values (1,323);
>  ORACLE is giving an error(check constraint...violated ORA-06512) for
> the above insert but Postgres is allowing it, however is not related
> to this patch but just thinking if this is expected.
>
> ‘postgres[22198]=#’SELECT * FROM T WHERE Jcol IS JSON;
>  id | jcol
> ----+------
>   1 | 323
> How come number 323 is the valid json?


If you look at the JSON grammar at <https://www.json.org/json-en.html>
or
<https://www.ecma-international.org/wp-content/uploads/ECMA-404_2nd_edition_december_2017.pdf>
it's clear that a bare number is valid json. Our parser implements that
grammar pretty faithfully, in fact rather more faithfully than many
implementations (e.g. we allow huge number strings). So as far as I'm
concerned, we are right and Oracle is wrong. It would hardly be the
first time such a thing has happened.

Oracle is not the definer of the JSON standard. ECMA is.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. JSON_TABLE: Add support for NESTED paths and columns

  2. Add basic JSON_TABLE() functionality

  3. Add SQL/JSON query functions

  4. Add soft error handling to some expression nodes

  5. Adjust populate_record_field() to handle errors softly

  6. Refactor code used by jsonpath executor to fetch variables

  7. Add more SQL/JSON constructor functions

  8. SQL/JSON: support the IS JSON predicate

  9. SQL/JSON: add standard JSON constructor functions

  10. Improve readability of SQL/JSON documentation.

  11. Documentation for SQL/JSON features

  12. RETURNING clause for JSON() and JSON_SCALAR()

  13. SQL JSON functions

  14. SQL/JSON query functions

  15. IS JSON predicate

  16. SQL/JSON constructors

  17. Common SQL/JSON clauses

  18. Doc: remove obsolete entries in table of BRIN strategy numbers.

  19. Doc: standardize markup a bit more.

  20. Make pg_regress.c unset PGDATABASE during make installcheck.

  21. Fix handling of GENERATED columns in CREATE TABLE LIKE INCLUDING DEFAULTS.