MySQL 的 INSERT IGNORE 語法可忽略重複主鍵錯誤,PostgreSQL 雖無內建此語法,但可透過規則、觸發器、CTE 或預存程序達成相同效果。本文以實例示範四種替代方案。
CREATE TABLE insert_ignore (
id integer NOT NULL,
log_time time without time zone,
PRIMARY KEY (id)
);
CREATE RULE r_insert_ignore AS
ON INSERT TO insert_ignore
WHERE EXISTS (SELECT 1 FROM insert_ignore WHERE id = NEW.id)
DO INSTEAD NOTHING;
測試:INSERT INTO insert_ignore VALUES (1, CURRENT_TIME), (2, CURRENT_TIME); 只會插入 id=2,id=1 被忽略。
CREATE OR REPLACE FUNCTION sp_insert_ignore() RETURNS TRIGGER AS $$
BEGIN
PERFORM 1 FROM insert_ignore WHERE id = NEW.id;
IF FOUND THEN
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_ib_insert_ignore
BEFORE INSERT ON insert_ignore
FOR EACH ROW EXECUTE PROCEDURE sp_insert_ignore();
INSERT INTO insert_ignore
WITH ytt_test(f1, f2) AS (
VALUES (6, CURRENT_TIME), (3, CURRENT_TIME)
)
SELECT a.* FROM ytt_test a
WHERE a.f1 NOT IN (SELECT id FROM insert_ignore b);
僅插入不存在的 id=6,忽略 id=3。
CREATE OR REPLACE FUNCTION sp_insert_ignore(
IN f_id int, IN f_log_time time without time zone
) RETURNS VOID AS $$
BEGIN
INSERT INTO insert_ignore VALUES (f_id, f_log_time);
EXCEPTION WHEN unique_violation THEN
RAISE NOTICE 'Duplicated Key Error on ID:%', f_id;
RETURN;
END;
$$ LANGUAGE plpgsql;
呼叫 SELECT sp_insert_ignore(1, '14:22:35'); 會提示重複,插入新 id 則成功。
以上四種方法皆能在 PostgreSQL 中模擬 INSERT IGNORE 行為,可根據專案需求選擇最適合的方案。