PostgreSQL – Fix ON CONFLICT ON CONSTRAINT Error

postgresqlpostgresql-9.5upsert

I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does).

My query is this

INSERT INTO journals (ext_ids, title) VALUES ('{"nlmid": "000"}', 'blah')
ON CONFLICT ON CONSTRAINT idx_nlmid_journal DO NOTHING;

where idx_nlmid_journal is unique index on jsonb field created like this

CREATE UNIQUE INDEX idx_nlmid_journal ON public.journals ((ext_ids ->> 'nlmid'::text));

I get error

ERROR: constraint "idx_nlmid_journal" for table "journals" does not exist

what am I missing ?

Best Answer

The syntax you use is not valid for a unique index because a unique index does not create a constraint. You need to remove the ON CONSTRAINT and use the index expression instead.

This works:

INSERT INTO journals (ext_ids, title) 
VALUES ('{"nlmid": "000"}', 'blah')
ON CONFLICT ((ext_ids ->> 'nlmid'::text)) 
DO NOTHING;

Related Solutions

PostgreSQL UNIQUE Constraint Error Message – How to Resolve

For instance, you can read these separate fields mentioned in your quote inside exception handlers in PL/pgSQL (as instructed in the manual, like @dezso commented):

DO
$$
DECLARE
   err_constraint text;

BEGIN
   INSERT INTO recipes (title) VALUES ('foo'), ('foo');  -- provoke unique violation

EXCEPTION
   WHEN SQLSTATE '23000' THEN  -- Class 23 — Integrity Constraint Violation
      GET STACKED DIAGNOSTICS err_constraint = CONSTRAINT_NAME;
      -- do something with it, for instance:
      RAISE NOTICE '%', err_constraint;
      RAISE;  -- raise original error

END;
$$

Result:

NOTICE:  title_unique

ERROR:  duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (title)=(foo) already exists.
CONTEXT:  SQL statement "INSERT INTO recipes (title) VALUES ('foo')"
PL/pgSQL function inline_code_block line 5 at SQL statement

Aside: you declare the unique constraints twice in your DDL command. (To my surprise, this results in a single constraint in my test on pg 9.5). Either way, do it only once.

CREATE TABLE recipes (
    id SERIAL,
    title TEXT NOT NULL,  -- remove redundant clause
    description TEXT NOT NULL,
    instructions TEXT NOT NULL,
    image BYTEA,
    CONSTRAINT recipes_pk PRIMARY KEY(id),
    CONSTRAINT title_unique UNIQUE(title)
);

PostgreSQL – Combining JSONB Query Statements

What I ended up doing using inspiration from MatheusOl answer.

INSERT INTO test (post_id, username, votes) 
VALUES (12345, 'testuser', '{"commentid" : {"vote": true }}'::jsonb) 
  ON CONFLICT ON CONSTRAINT test_pkey DO UPDATE

  SET votes = test.votes || coalesce(test.votes->'commentid', '{"commentid" : {"vote: true"}}'))
   || jsonb_set(test.votes, '{commentid,vote}', 'true'::jsonb) 

WHERE NOT test.votes @> EXCLUDED.votes
RETURNING *;

When using his answer, "commentid" would still be overwritten, but I don't really understand why.

This doesn't do things very efficiently, as it recreates the "commentid" key every time as a new object, but in my case it will have significantly more reads than writes, so it should be fine

Best Answer

Related Solutions

PostgreSQL UNIQUE Constraint Error Message – How to Resolve

PostgreSQL – Combining JSONB Query Statements

Related Question