HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlModerate

ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
failsexistsayingdoesnconstraintconflict

Problem

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 ?

Solution

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;

Code Snippets

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

Context

StackExchange Database Administrators Q#139756, answer score: 14

Revisions (0)

No revisions yet.