13

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?

Randy Layman
  • 313
  • 3
  • 10

2 Answers2

13

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
Nick
  • 7,103
  • 2
  • 21
  • 43
  • That doesn't seem to work. After adding the index I get "ERROR: column "tbl_data_a_uidx" does not exist" – Randy Layman Aug 16 '16 at 17:05
  • I'm using PG 10.3 and it seems to require quoting the jsonb field: `ON CONFLICT ((data->>'a'))` – cjauvin Mar 23 '18 at 18:21
  • 1
    @cjauvin - You are correct - thanks for catching that. I've updated the answer accordingly. – Nick Mar 28 '18 at 20:35
  • I did exactly as you wrote and got an error witch says : there is no unique or exclusion constraint matching the ON CONFLICT specification – Karam Haj Sep 26 '19 at 09:17
  • @KaramHaj - The error message is pretty clear. You don't have a constraint created on the table that matches the specification/expression in the `ON CONFLICT`. You need to create one. – Nick Sep 26 '19 at 14:40
0

Read through this PostgreSQL documentation on this subject. You might want to use the json_populate_record function to fill the table if you are trying to build a json parser. Also see the related question: How to perform update operations on columns of type JSONB in Postgres 9.4

Community
  • 1
  • 1
Aaron Morefield
  • 952
  • 10
  • 18