PostgreSQL: CHECK constraint for non-empty JSON objects

Question

I want to have a CHECK constraint on a JSONB column that only permits non-empty JSON objects (only {} with attributes, no other values like [] or JSON primitives).

I only want to check the "root" value, it doesn't matter what is stored in these objects.

How can I do that?

steamer25 · Accepted Answer · 2019-07-03 19:30:19Z

3

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

edited Jul 3, 2019 at 19:30

steamer25

9,6731 gold badge35 silver badges38 bronze badges

answered Sep 17, 2015 at 16:50

snøreven

1,9742 gold badges20 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Heikens · Accepted Answer · 2015-01-08 20:23:02Z

1

Just like any check constraint and use the <> 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 you the not equal operator <>:

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

answered Jan 8, 2015 at 20:23

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

5 Comments

snøreven Over a year ago

That's not what I asked. It only prohibits '{}'. I'd still be able to insert '[]' or 'true' for example.

Frank Heikens Over a year ago

Then put these conditions also in the check constraint using an OR

snøreven Over a year ago

Well it was basically the question how to do that exactly. But I realised I just overlooked the json_typeof() function - that's why I couldn't just OR it because I had no idea with what. Working now, thanks!

Ian Storm Taylor Over a year ago

@snøreven what was your solution in the end?

snøreven Over a year ago

@ian-storm-taylor I just wrote an answer.

Collectives™ on Stack Overflow

PostgreSQL: CHECK constraint for non-empty JSON objects

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related