May be a jsonb type bug
Hi all:
When i exec function jsonb_array_length(jsonb)，The database throw a
error: |cannot get array length of a scalar
That's because have a 'null' value in the jsonb type column|
|So i have a test and think null is a bug for jsonb:
|
mydb=# \d test
Table "public.test"
Column | Type | Collation | Nullable
| Default
----------+-----------------------------+-----------+----------+----------------------------------
id | integer | | not null |
nextval('test_id_seq'::regclass)
info | text | | |
crt_time | timestamp without time zone | | |
col_n | jsonb | | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
mydb=# update test set col_n='test' where id=1;
ERROR: invalid input syntax for type json
LINE 1: update test set col_n='test' where id=1;
^
DETAIL: Token "test" is invalid.
CONTEXT: JSON data, line 1: test
mydb=# update test set col_n='null' where id=1;
UPDATE 1
mydb=#
mydb=#
mydb=# select * from test where id=1;
id | info | crt_time | col_n
----+------+----------------------------+-------
1 | test | 2017-12-13 15:14:16.347681 | null
(1 row)
--
*PostgreSQL DBA yunlong.gao *
pg开发指南 http://wiki.corp.qunar.com/pages/viewpage.action?pageId=58058230
pg发布流程 http://wiki.corp.qunar.com/pages/viewpage.action?pageId=56215301
pg值班列表 http://wiki.corp.qunar.com/pages/viewpage.action?pageId=50508626
pg机器列表 http://wiki.corp.qunar.com/pages/viewpage.action?pageId=36438672
pgbouncer http://wiki.corp.qunar.com/display/searchdev/pgbouncer+server
Hi
'null' - is valid JSON document, but not array or object. It is just JSON-null in JSON. (not NULL in terms RDBMS)
Same as '"test"' - is valid JSON-encoded string 'test', '10' - valid JSON int scalar.
jsonb_array_length accept only JSON array documents: '[1,2,3]', '[null, null]', '[]', '[{"n":1}]' and so on and not accept scalars and objects
Regards, Sergej
Thank you very much
在 2017年12月22日 21:04, Sergei Kornilov 写道:
Hi
'null' - is valid JSON document, but not array or object. It is just JSON-null in JSON. (not NULL in terms RDBMS)
Same as '"test"' - is valid JSON-encoded string 'test', '10' - valid JSON int scalar.jsonb_array_length accept only JSON array documents: '[1,2,3]', '[null, null]', '[]', '[{"n":1}]' and so on and not accept scalars and objects
Regards, Sergej
--
*PostgreSQL DBA yunlong.gao *
pg开发指南 http://wiki.corp.qunar.com/pages/viewpage.action?pageId=58058230
pg发布流程 http://wiki.corp.qunar.com/pages/viewpage.action?pageId=56215301
pg值班列表 http://wiki.corp.qunar.com/pages/viewpage.action?pageId=50508626
pg机器列表 http://wiki.corp.qunar.com/pages/viewpage.action?pageId=36438672
pgbouncer http://wiki.corp.qunar.com/display/searchdev/pgbouncer+server