ERROR: pq: there is no unique or exclusion constraint matching the ON CONFLICT specification-CodePudding

I have this visitor table structure like below and I have created the upsert procedure to this table. When I call the procedure it returns ERROR: pq: there is no unique or exclusion constraint matching the ON CONFLICT specification. Can anyone please suggest the required changes.


CREATE TABLE visitors
(
    id               SERIAL    NOT NULL,

    facebook         TEXT      DEFAULT NULL,
    github           TEXT      DEFAULT NULL,
    linkedin         TEXT      NOT NULL,
    twitter          TEXT      DEFAULT NULL
);

ALTER TABLE visitors ADD CONSTRAINT visitors_primary_key PRIMARY KEY (id);

ALTER TABLE visitors ADD CONSTRAINT visitors_urls UNIQUE (facebook, github, linkedin, twitter);

The upsert procedure:

CREATE OR REPLACE PROCEDURE visitors_upsert
(
    _facebook             TEXT      DEFAULT NULL,
    _github               TEXT      DEFAULT NULL,
    _linkedin             TEXT      DEFAULT '',
    _twitter              TEXT      DEFAULT NULL
)
LANGUAGE SQL
AS $$
    INSERT INTO visitors
    (
        facebook,
        github,
        linkedin
    )
    VALUES
    (
        _facebook,
        _github,
        _linkedin,
        _twitter
    )
    ON CONFLICT (linkedin)
    DO UPDATE 
    SET
        facebook = _facebook,
        github = _github,
        twitter = _twitter
$$;

CALL visitors_upsert('facebook', 'github', 'linkedin', 'twitter')

Demo: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=21ed4fa7526a0744feecbbe43f8944f6

CodePudding user response：

There is no unique constraint on linkedin. Either create one, or use something else than INSERT ... ON CONFLICT.