postgreSQL Adding New Key with value if it doesnt exist and edit it if it does

Question

I'm trying to run the following but it's not updating columns with NULL values or columns that do not have this key:

update A a set 
a.jsonbcolumn = ('{"key":' 1 '}')

I'm using postgres 9.6.3 and jsonb_set does not work for me. Any advice?

Thanks

score 5 · Accepted Answer · 2018-10-31 10:23:01Z

5

jsonb_set() is the way to go:

update a
  set jsonbcolumn = jsonb_set(jsonbcolumn, '{key}', '1');

If it does not create the key, this is probably becase the column value is NULL rather than an empty JSON. In that case use coalesce():

update a
  set jsonbcolumn = jsonb_set(coalesce(jsonbcolumn,'{}'), '{key}', '1');

answered Oct 31, 2018 at 10:09

user330315

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

1 Answer 1