Re: remaining sql/json patches
От | Tomas Vondra |
---|---|
Тема | Re: remaining sql/json patches |
Дата | |
Msg-id | f1084e8b-17f2-4e1f-9d75-fbb48eb75280@enterprisedb.com обсуждение исходный текст |
Ответ на | Re: remaining sql/json patches (Himanshu Upadhyaya <upadhyaya.himanshu@gmail.com>) |
Ответы |
Re: remaining sql/json patches
|
Список | 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 по дате отправления:
Предыдущее
От: David RowleyДата:
Сообщение: Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY