PostgreSQL: Update query for JSONB columns with examples

Introduction

In PostgreSQL 9.5 the data type JSONB was added. In this short tutorial, we are goin to show how to make update queries agains a jsonb type.

JSONB update example

There are many ways to update data on jsonb column, the most simple one is using a valid json between single quotes:

UPDATE example SET data='{"example_key": "value in example_key"}';

Note: here we use double quotes on the json data, since json uses double quotes.

Update or insert

If you want to update or insert the dictionary you can use the operator ||:

UPDATE example
SET data = data || {"example_key_2": "new value"}

Example table

We are going to use a simple example table with an ID and DATA (jsonb)

Here is how to create the example table which uses jsonb:

CREATE TABLE example (
  ID         BIGSERIAL PRIMARY KEY,
  DATA       JSONB
);