string \u0000 is disallowed, as are Unicode escapes representing characters not available in the database encoding number numeric NaN and infinity values are disallowed boolean boolean Only lowercase true and false spellings are accepted (none) SQL NULL is a different concept

you can store any text value except \u0000 - which a Unicode escapes representing characters that represent NULL

to show the meaning of Unicode characters in Postgres use E in front of text like

----------------------- jsonb string ---------------------------
select E'\u0001' as value;
-- value 
-- -------
-- \x01
-- (1 row)
select E'\u0000' as value;
-- ERROR: invalid Unicode escape value at or near "E'\u0000"
-- LINE 1: select E'\u0000' as value;
SELECT '1234\u0000';
-- ?column? 
-- ------------
-- 1234\u0000
-- (1 row)
SELECT E'My star \u2B50';
--  ?column?  
-- -----------
--  My star ⭐
-- (1 row)
SELECT E'1234\u0000';
-- ERROR: invalid Unicode escape value at or near "E'1234\u0000"
-- LINE 1: SELECT E'1234\u0000';
SELECT '"1234\u0000"'::jsonb;
-- ERROR: unsupported Unicode escape sequence
-- LINE 1: SELECT '"1234\u0000"'::jsonb;
-- DETAIL: \u0000 cannot be converted to text.
-- CONTEXT: JSON data, line 1: ...
SELECT '"My face \u2B50"'::jsonb;
--     jsonb    
-- -------------
--  "My face ⭐"
-- (1 row)
    
----------------------- jsonb number ---------------------------
SELECT jsonb_typeof('1'::jsonb);
-- jsonb_typeof 
-- --------------
-- number
-- (1 row)
SELECT jsonb_typeof('-1'::jsonb);
-- jsonb_typeof 
-- --------------
-- number
-- (1 row)
    
SELECT jsonb_typeof('inf'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('inf'::jsonb);
-- DETAIL: Token "inf" is invalid.
-- CONTEXT: JSON data, line 1: inf
SELECT jsonb_typeof('-inf'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('-inf'::jsonb);
-- DETAIL: Token "-inf" is invalid.
-- CONTEXT: JSON data, line 1: -inf
    SELECT jsonb_typeof('NAN'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('NAN'::jsonb);
-- DETAIL: Token "NAN" is invalid.
-- CONTEXT: JSON data, line 1: NAN
    SELECT jsonb_typeof('False'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('False'::jsonb);
-- DETAIL: Token "False" is invalid.
-- CONTEXT: JSON data, line 1: False
SELECT jsonb_typeof('True'::jsonb);
-- ERROR: invalid input syntax for type json
-- LINE 1: SELECT jsonb_typeof('True'::jsonb);
-- DETAIL: Token "True" is invalid.
-- CONTEXT: JSON data, line 1: True
SELECT jsonb_typeof('"True"'::jsonb);
-- jsonb_typeof 
-- --------------
-- string
-- (1 row)
    
jsonb null is different from NULL in Postgres as SQL’s null means “of unknown value” and JSON’s null means “empty/no value”. 
let’s take some examples to explain this:
Note : use -> to return the value of key in jsonb as jsonb type and ->> convert the value of key as text and return text.    . By the way I explained this in next post in this series

select '{"a": 1, "b": null}'::jsonb->'c';
-- ?column? 
-- ----------
-- (1 row)
    
c is not keys in jsonb so if we ask for it is value Postgres will search for it in '{"a": 1, "b": null}' and as it can not find it so Postgres does not know the value of c so it says it is  unknown value means it is NULL, as c does not there
Note : in select Null does not shown

select '{"a": 1, "b": null}'::jsonb->'c' IS NULL;
-- ?column? 
-- ----------
-- (1 row)
    
One of the design principles of PostgreSQL, however, is that casting anything to text should give something parsable back to the original value (whenever possible).
So if we will convert null to text it will be NULL as it is the nearest value to it that we can use to re-parse it back … 
but why we can not just convert null to be ‘null’ as string with 2 single quotes ??!!
doing something like that will make confused between ‘null’string value that stored as strung in keys like

select '{"a": 1, "b": null}'::jsonb->>'b';
-- ?column? 
-- ----------
-- (1 row)
select '{"a": 1, "b": "null"}'::jsonb->>'b';
-- ?column? 
-- ----------
-- null
-- (1 row)
select pg_typeof('{"a": 1, "b": "null"}'::jsonb->>'b');
-- pg_typeof 
-- -----------
-- text
-- (1 row)
select pg_typeof('{"a": 1, "b": null}'::jsonb->>'b');
-- pg_typeof 
-- -----------
-- text
-- (1 row)
    
so ‘null’ to text = ‘null’ and if you re-parse ‘null’ to original type it will string ‘null’
but null to text = NULL and if you re-parse null to original type it will null 
this lead us to golden point here so if you want to check that if key does not there or its value is null use ->> to get value of that key and check if IS NULL ….
same if you want to say if value is there for keys this means the key is there or its value is not null use ->> to get value of that key and check if IS NOT NULL
Reference : Check this for more details

PostgreSQL_and_null_value
      
Built on Forem — the open source software that powers DEV and other inclusive communities.
      
Made with love and Ruby on Rails. DEV Community © 2016 - 2024.