postgresql - 修复对 PostgreSQL 的请求，没有 UNIQUE 规则的异常

Question

我有两个表:

CREATE TABLE recruiter (
        nickname TEXT PRIMARY KEY NOT NULL,
        url_account TEXT NOT NULL,
        UNIQUE (nickname)
);

CREATE TABLE proposal (
        header TEXT NOT NULL,
        url_proposal TEXT NOT NULL,
        time_addition TIMESTAMP NOT NULL,
        nickname TEXT NOT NULL,
        UNIQUE (url_proposal, header),
        FOREIGN KEY (nickname) REFERENCES recruiter (nickname)
);

然后我用脚本插入提案表数据:

INSERT INTO proposal (header, url_proposal, time_addition, nickname) VALUES ((?), (?), (?), (?))

有时我有重复和规则 UNIQUE (url_proposal, header) 抛出:

PSQLException: ERROR: duplicate key value violates unique constraint "proposal_url_proposal_header_key"

请帮我修复我的脚本，这样就不会抛出异常，但脚本的工作方式相同。没有重复，没有 Exception 在提案表中插入我的数据 (header, url_proposal, time_addition, nickname) 在一个请求中。

Answer 1

如果你想拒绝新行:

INSERT INTO proposal (header, url_proposal, time_addition, nickname) 
VALUES ('header', 'url', now(), 'nick')
ON CONFLICT (url_proposal, header) DO NOTHING;

或者如果你想更新现有的行:

INSERT INTO proposal (header, url_proposal, time_addition, nickname) 
VALUES ('header', 'url', now(), 'nick')
ON CONFLICT (url_proposal, header)
DO UPDATE SET time_addition = excluded.time_addition, nickname = excluded.nickname;