debugsqlModerate
ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist
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
where
I get error
what am I missing ?
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 thisCREATE 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 existwhat 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
This works:
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.