Postgres INSERT ON CONFLICT with JSONB

Question

I'm trying to use Postgres as a document store and am running into a problem when I'm trying to effectively upsert a document where the Postgres parser doesn't seem to like the JSONB operator.

I have a table:

CREATE TABLE tbl (data jsonb NOT NULL);
CREATE UNIQUE INDEX ON tbl ((data->>'a'));

and I try to insert data with:

INSERT INTO tbl (data) VALUES ('{ "a": "b" }'::jsonb) 
  ON CONFLICT (data->>a) 
  DO UPDATE SET data = data || '{ "a": "b" }'::jsonb

I get this error message:

ERROR:  syntax error at or near "->>"

I've tried data->>a, data->>'a', data->a, and maybe data->'a'. All of those are

I'd like to leave the identifier column (a in the example) within the JSON and not make it a column on the table.

Is what I'm trying to do currently supported?

Nick Nick · Accepted Answer · 2016-08-16T17:00:44

There are two issues you have:

1) You need to add additional parenthesis, like so:

ON CONFLICT ((data->>'a'))

2) You need to preface the last data reference with your table alias, like so:

DO UPDATE SET data = tbl.data || '{ "a": "b" }'::jsonb

Postgres INSERT ON CONFLICT with JSONB

2 Answers