How to have not null constraint inside a jsonb column in Postgres.
I have created a Postgres table with just one column called id like this below
create table tablea (
id jsonb,
check
((id->>'test1', id->>'test2') != (null, null))
);
The caller will insert data into the table in the below json format:-
[
{
"test1":"",
"test2":"",
"test3":""
},
{
"test1":"",
"test2":"",
"test3":""
}
]
My goal is to when a caller insert data in the id column i want the key test1 and test2 be not null. How can in achieve that. My table creation logic is explained above. I am trying to insert data like
insert into tablea(id) values
('[{"test1":null,"test2":"a","test3":""}]');
Ideally this insert statement should throw me error but it is inserting data in the table. Can anyone help me out
You will need to create a function that iterates through your array and validates every array element.
Something like this:
create or replace function validate_json(p_input jsonb)
returns boolean
as
$$
select not exists (select *
from jsonb_array_elements(p_input) as t(element)
where nullif(element ->> 'test1', '') is null
or nullif(element ->> 'test2', '') is null);
$$
language sql
stable;
Then you can use it to define a check constraint:
You can't compare null with = or <>. You need to use IS NOT NULL for that.
It also seems you want to treat an empty string the same way as null.
create table tablea
(
id jsonb,
constraint check_json check ( validate_json(id) )
);