18

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 ?

expert
  • 285
  • 1
  • 2
  • 7

2 Answers2

15

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;
ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
1

I was getting the same issue. Here is my solution:

CREATE TABLE messages_tags
(
    id SERIAL PRIMARY KEY,
    message_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL
);

ALTER TABLE messages_tags 
    ADD CONSTRAINT messages_tags_message_id_fkey FOREIGN KEY
    (
        message_id
    ) REFERENCES messages (
        id
    )
    ON UPDATE CASCADE
    ON DELETE CASCADE
;

ALTER TABLE messages_tags 
    ADD CONSTRAINT messages_tags_tag_id_fkey FOREIGN KEY
    (
        tag_id
    ) REFERENCES tags (
        id
    )
    ON UPDATE CASCADE
    ON DELETE CASCADE
;


CREATE UNIQUE INDEX idx_messages_tags ON messages_tags (message_id, tag_id);

ALTER TABLE messages_tags 
ADD CONSTRAINT unique_messages_tags 
UNIQUE
USING INDEX idx_messages_tags;

INSERT INTO messages_tags (message_id, tag_id) VALUES ($1, $2) ON CONFLICT  ON CONSTRAINT unique_messages_tags  DO NOTHING
Victor Ray
  • 11
  • 1