Postgres INSERT ON CONFLICT with JSONB

Question

I'm trying to use Postgres as a document repository and run into a problem when I try to efficiently update a document where the Postgres parser does not seem to be like a JSONB statement.

I have a table:

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

and I'm trying to insert data using:

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 tried the data →> a, data →> 'a', data-> a, or maybe the data → 'a'. All this

I would like to leave the identifier column (a in the example) in JSON and not make it a column in the table.

What I'm trying to do is currently supported?

Answer 1

There are two problems:

1) You need to add additional brackets, for example:

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

2) data , :

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

Answer 2

PostgreSQL . , json_populate_record, , json. . : JSONB Postgres 9.4