Re: remaining sql/json patches - Mailing list pgsql-hackers
From | Tomas Vondra |
---|---|
Subject | Re: remaining sql/json patches |
Date | |
Msg-id | f1084e8b-17f2-4e1f-9d75-fbb48eb75280@enterprisedb.com Whole thread Raw |
In response to | Re: remaining sql/json patches (Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>) |
Responses |
Re: remaining sql/json patches
|
List | pgsql-hackers |
On 3/7/24 06:18, Himanshu Upadhyaya wrote: > On Wed, Mar 6, 2024 at 9:04 PM Tomas Vondra <tomas.vondra@enterprisedb.com> > wrote: > >> >> >> I'm pretty sure this is the correct & expected behavior. The second >> query treats the value as string (because that's what should happen for >> values in double quotes). >> >> ok, Then why does the below query provide the correct conversion, even if > we enclose that in double quotes? > ‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{ > "id" : "1234567890", > "FULL_NAME" : "JOHN DOE"}', > '$' > COLUMNS( > name varchar(20) PATH 'lax $.FULL_NAME', > id int PATH 'lax $.id' > ) > ) > ; > name | id > ----------+------------ > JOHN DOE | 1234567890 > (1 row) > > and for bigger input(string) it will leave as empty as below. > ‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{ > "id" : "12345678901", > "FULL_NAME" : "JOHN DOE"}', > '$' > COLUMNS( > name varchar(20) PATH 'lax $.FULL_NAME', > id int PATH 'lax $.id' > ) > ) > ; > name | id > ----------+---- > JOHN DOE | > (1 row) > > seems it is not something to do with data enclosed in double quotes but > somehow related with internal casting it to integer and I think in case of > bigger input it is not able to cast it to integer(as defined under COLUMNS > as id int PATH 'lax $.id') > > ‘postgres[102531]=#’SELECT * FROM JSON_TABLE(jsonb '{ > "id" : "12345678901", > "FULL_NAME" : "JOHN DOE"}', > '$' > COLUMNS( > name varchar(20) PATH 'lax $.FULL_NAME', > id int PATH 'lax $.id' > ) > ) > ; > name | id > ----------+---- > JOHN DOE | > (1 row) > ) > > if it is not able to represent it to integer because of bigger input, it > should error out with a similar error message instead of leaving it empty. > > Thoughts? > Ah, I see! Yes, that's a bit weird. Put slightly differently: test=# SELECT * FROM JSON_TABLE(jsonb '{"id" : "2000000000"}', '$' COLUMNS(id int PATH '$.id')); id ------------ 2000000000 (1 row) Time: 0.248 ms test=# SELECT * FROM JSON_TABLE(jsonb '{"id" : "3000000000"}', '$' COLUMNS(id int PATH '$.id')); id ---- (1 row) Clearly, when converting the string literal into int value, there's some sort of error handling that realizes 3B overflows, and returns NULL instead. I'm not sure if this is intentional. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
pgsql-hackers by date: