背景
此问题涉及使用Postgresql 9.2或更高版本忽略重复插入.我问的原因是因为这段代码：
-- Ignores duplicates.
INSERT INTO
db_table (tbl_column_1,tbl_column_2)
VALUES (
SELECT
unnseted_column,param_association
FROM
unnest( param_array_ids ) AS unnested_column
);
检查现有值时,代码不受阻碍. (在这种特殊情况下,用户不关心插入重复项的错误 – 插入应该“正常工作”.)在这种情况下添加代码以明确测试重复项会带来复杂性.
问题
忽略重复#1
创建一个捕获唯一约束违规的事务,不执行任何操作：
BEGIN
INSERT INTO db_table (tbl_column) VALUES (v_tbl_column);
EXCEPTION WHEN unique_violation THEN
-- Ignore duplicate inserts.
END;
忽略重复#2
创建规则以忽略给定表上的重复项：
CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
ON INSERT TO db_table
WHERE (EXISTS ( SELECT 1
FROM db_table
WHERE db_table.tbl_column = NEW.tbl_column)) DO INSTEAD NOTHING;
问题
我的问题主要是学术问题：
>哪种方法最有效？
>哪种方法最易维护,为什么？
>使用Postgresql忽略插入重复错误的标准方法是什么？
>是否存在技术上更有效的方法来忽略重复插入;如果是的话,它是什么？
谢谢！
UPSERT功能.对于旧版本,请继续阅读:)
我已经设置了检查选项的测试.我将包含下面的代码,它可以在linux / Unix机器上的psql中运行(仅仅因为为了结果清晰起见,我将设置命令的输出传送到/ dev / null – 在Windows机器上可以选择一个日志文件).
我试图通过在plpgsql存储过程中使用一个循环来运行每种类型多个(即100个)INSERT来使不同的结果具有可比性.此外,在每次运行之前,通过截断并重新插入原始数据来重置表.
检查一些测试运行,看起来像使用规则并显式添加WHERE NOT EXISTS INSERT语句花费相似的时间,而导致异常需要花费更多的时间才能完成.
后者不是surprising：
Tip: A block containing an EXCEPTION clause is significantly more
expensive to enter and exit than a block without one. Therefore,don’t
use EXCEPTION without need.
就个人而言,由于可读性和可维护性,我更喜欢将INSERE NOT EXISTS位添加到INSERT本身.就像触发器(也可以在这里测试)一样,调试(或简单地跟踪INSERT行为)在存在规则时更复杂.
我使用的代码(随意指出误解或其他问题)：
\o /dev/null
\timing off
-- set up data
DROP TABLE IF EXISTS insert_test;
CREATE TABLE insert_test_base_data (
id integer PRIMARY KEY,col1 double precision,col2 text
);
CREATE TABLE insert_test (
id integer PRIMARY KEY,col2 text
);
INSERT INTO insert_test_base_data
SELECT i,(SELECT random() AS r WHERE s.i = s.i)
FROM
generate_series(2,200,2) s(i)
;
UPDATE insert_test_base_data
SET col2 = md5(col1::text)
;
INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;
-- function with exception block to be called later
CREATE OR REPLACE FUNCTION f_insert_test_insert(
id integer,col2 text
)
RETURNS void AS
$body$
BEGIN
INSERT INTO insert_test
VALUES ($1,$2,$3)
;
EXCEPTION
WHEN unique_violation
THEN NULL;
END;
$body$
LANGUAGE plpgsql;
-- function running plain sql ... WHERE NOT EXISTS ...
CREATE OR REPLACE FUNCTION insert_test_where_not_exists()
RETURNS void AS
$body$
BEGIN
FOR i IN 1 .. 100
LOOP
INSERT INTO insert_test
SELECT i,rnd,md5(rnd::text)
FROM (SELECT random() AS rnd) r
WHERE NOT EXISTS (
SELECT 1
FROM insert_test
WHERE id = i
)
;
END LOOP;
END;
$body$
LANGUAGE plpgsql;
-- call a function with exception block
CREATE OR REPLACE FUNCTION insert_test_function_with_exception_block()
RETURNS void AS
$body$
BEGIN
FOR i IN 1 .. 100
LOOP
PERFORM f_insert_test_insert(i,md5(rnd::text))
FROM (SELECT random() AS rnd) r
;
END LOOP;
END;
$body$
LANGUAGE plpgsql;
-- leave checking existence to a rule
CREATE OR REPLACE FUNCTION insert_test_rule()
RETURNS void AS
$body$
BEGIN
FOR i IN 1 .. 100
LOOP
INSERT INTO insert_test
SELECT i,md5(rnd::text)
FROM (SELECT random() AS rnd) r
;
END LOOP;
END;
$body$
LANGUAGE plpgsql;
\o
\timing on
\echo
\echo 'check before INSERT'
SELECT insert_test_where_not_exists();
\echo
\o /dev/null
\timing off
TRUNCATE insert_test;
INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;
\timing on
\o
\echo 'catch unique-violation'
SELECT insert_test_function_with_exception_block();
\echo
\echo 'implementing a RULE'
\o /dev/null
\timing off
TRUNCATE insert_test;
INSERT INTO insert_test
SELECT *
FROM insert_test_base_data
;
CREATE OR REPLACE RULE db_table_ignore_duplicate_inserts AS
ON INSERT TO insert_test
WHERE EXISTS (
SELECT 1
FROM insert_test
WHERE id = NEW.id
)
DO INSTEAD NOTHING;
\o
\timing on
SELECT insert_test_rule();
