postgresql: how to define a JSONB column with default value

Question

I am unable to find in the documentation how to create a JSONB column in PostgreSQL that has a DEFAULT value of an empty json document.

How the above can be stated in the CREATE TABLE definition ?

what's an empty json document? "" contrains a string {} contains an object null contains a null ? — Jasen, May 19 '18 at 02:58

score 77 · Accepted Answer · answered May 17 '18 at 09:02

77

That's the same as with any other default value:

create table amsterdam
(
   id       integer primary key, 
   payload  jsonb not null default '{}'::jsonb
);

answered May 17 '18 at 09:02

1

How to set a default value other than {} ? .e.g [1,2,3] ? – Eric Aug 27 '22 at 03:43
How to define default value for jsonb[] ? – Abhay Jun 20 '23 at 09:33

score 22 · Answer 2 · edited Jan 17 '23 at 18:09

22

If you are altering an already existing table, then the syntax is as follows:

ALTER TABLE my_table ADD COLUMN my_column JSONB NOT NULL DEFAULT '{}'::jsonb;

edited Jan 17 '23 at 18:09

Andrii Abramov

answered Jul 04 '19 at 14:46

enomad

2

How is this different from the existing accepted answer? – mustaccio Jul 04 '19 at 18:25
3

The captain here! alter vs create – Valery Bugakov Nov 10 '19 at 11:17

2 Answers2