背景

此问题与使用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忽略插入重复错误的标准方法是什么?
是否存在一种技术上更有效的方法来忽略重复插入?如果是这样,那是什么?

谢谢!

评论

我认为根本不尝试插入重复项是解决您的问题的最佳解决方案。检查是否存在给定键值,例如INSERT ... SELECT / *而不是VALUES! * / ...在哪里...

您的第一个INSERT语句不检查任何内容(而且语法也不正确)

#1 楼

作为另一个问题的答案(此问题被视为重复),提到了(自9.5版起)本机UPSERT功能。对于较旧的版本,请继续阅读:)

我已经建立了一个检查选项的测试。我将包含以下代码,该代码可以在Linux / Unix机壳上的psql中运行(因为为了使结果更清晰,我将设置命令的输出通过管道传输到/dev/null-在Windows机壳上,可以选择而是一个日志文件)。

我试图通过在INSERT存储过程内的循环中使用每种类型使用多个(即100个)plpgsql来使不同的结果具有可比性。此外,在每次运行之前,都会通过截断并重新插入原始数据来重置表。

检查一些测试运行,看起来就像使用规则并显式添加WHERE NOT EXISTSINSERT语句花费相似的时间,而处理异常则需要花费更多的时间才能完成。

后者并不奇怪:没有一个块。因此,不要
不需要使用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