背景
此问题与使用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
);
该代码不受检查现有值的限制。 (在这种特殊情况下,用户不必担心插入重复项会产生错误-插入应该“正常”。)在这种情况下添加代码以明确测试重复项会带来复杂性。 br />
在PostgreSQL中,我发现了几种忽略重复插入的方法。
忽略重复项#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忽略插入重复错误的标准方法是什么?
是否存在一种技术上更有效的方法来忽略重复插入?如果是这样,那是什么?
谢谢!
#1 楼
作为另一个问题的答案(此问题被视为重复),提到了(自9.5版起)本机UPSERT
功能。对于较旧的版本,请继续阅读:) 我已经建立了一个检查选项的测试。我将包含以下代码,该代码可以在Linux / Unix机壳上的
psql
中运行(因为为了使结果更清晰,我将设置命令的输出通过管道传输到/dev/null
-在Windows机壳上,可以选择而是一个日志文件)。 我试图通过在
INSERT
存储过程内的循环中使用每种类型使用多个(即100个)plpgsql
来使不同的结果具有可比性。此外,在每次运行之前,都会通过截断并重新插入原始数据来重置表。检查一些测试运行,看起来就像使用规则并显式添加
WHERE NOT EXISTS
或INSERT
语句花费相似的时间,而处理异常则需要花费更多的时间才能完成。 后者并不奇怪:没有一个块。因此,不要
不需要使用EXCEPTION。
就个人而言,由于其可读性和可维护性,我更喜欢在
WHERE 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,
col1 double precision,
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,
col1 double precision,
col2 text
)
RETURNS void AS
$body$
BEGIN
INSERT INTO insert_test
VALUES (, , )
;
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, rnd, 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, rnd, 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();
评论
这次真是万分感谢。将EXISTS添加到每个INSERT语句的唯一问题是,它可能导致代码重复。我使用单个存储过程包装INSERT语句,但是我可以想象在代码库中存在多个INSERT语句并因此存在多个检查的情况。
–戴夫·贾维斯(Dave Jarvis)
2012-12-17 17:24
@DaveJarvis:说到数据验证,重复和冗余并不是(必要)坏事
–araqnid
2012年12月17日在19:10
@DaveJarvis如果将INSERT集中到存储过程中,则可以。如果不这样做,则在有或没有存在检查的情况下重复执行INSERT ...
– dezso
2012年12月17日在19:25
评论
我认为根本不尝试插入重复项是解决您的问题的最佳解决方案。检查是否存在给定键值,例如INSERT ... SELECT / *而不是VALUES! * / ...在哪里...您的第一个INSERT语句不检查任何内容(而且语法也不正确)