There is no unique or exclusion constraint matching the ON CONFLICT specification

Table copy_shop Column data has type jsonb

If exist record with data.name = myStore then I need to update record. Else I need insert json.

{
    "name": "myStore",
    "coords": {
        "lat": "47.123456",
        "lon": "35.7890"
    }
}

I try this:

INSERT INTO copy_shop (data) 
VALUES 
('{
    "name": "myStore",
    "coords": {
        "lat": "47.123456",
        "lon": "35.7890"
    }
}')
ON CONFLICT ((data->'name')) 
DO UPDATE SET data = copy_shop.data || '{
    "name": "myStore",
    "coords": {
        "lat": "47.123456",
        "lon": "35.7890"
    }
}'::jsonb

But I get error:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
SQL state: 42P10


Read more here: https://stackoverflow.com/questions/64895505/there-is-no-unique-or-exclusion-constraint-matching-the-on-conflict-specificatio

Content Attribution

This content was originally published by a_subscriber at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: