Clever Geek Handbook

PostgreSQL: CHECK constraint for non-empty JSON objects

I want to have a CHECK constraint on a JSONB column that allows non-empty JSON objects (only {}

with attributes, other values ​​like []

or JSON primitives).

I only want to check the value for "root", no matter what is stored in these objects.

How can i do this?

+4


source to share


2 answers 


CHECK(jsonb_typeof(foo)='object' AND foo <> '{}'::JSONB)


+3


source


Just like any validation constraint and use of <> operators. From the manual:

The standard comparison operators shown in Table 9-1 are available for jsonb, but not for json.



And table 9.1 shows the operator not equal to <>:

create table foo(
    bar jsonb,
    constraint baz check(bar <> '{}'::jsonb)
);

insert into foo(bar) values('{"foo": 1}'::jsonb);
insert into foo(bar) values('{}'::jsonb); -- fails
+1


source






More articles:


All Articles