Continue executing INSERT script even on error - PostgreSQL using Python script

Question

I'm trying to make a bulk insert into my PostgreSQL DB using a python script (with psycopg connection). Now, the script has some duplicates on the primary column which makes it abort and rollback the entire script. However, what I want is to skip the duplicates and continue execution. My script looks something like the below

begin transaction;
create table t1 (c1 float, unique(c1));
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (1); --the script aborts and rolls back
commit;

I have looked around and found out that setting the ON_ERROR_ROLLBACK helps. But it seems like, it can be set only from the psql command line.

Is it possible to use ON_ERROR_ROLLBACK from inside the python script? Or is there any other alternative option?

Thanks in advance!

score 2 · Answer 1 · answered Dec 16 '15 at 16:53

Usually transactions are used to return to a SAVEPOINT. In your case, you could just use plain sql, not in a transaction. So each statement will be wrapped in BEGIN - COMMIT block implicitly.

INSERT INTO t1(c1) VALUES (1);
INSERT INTO t1(c1) VALUES (2);
INSERT INTO t1(c1) VALUES (1);

If you still want to use a single transaction, you could write a function and use exception handling:

CREATE OR REPLACE FUNCTION insertIntoT1(key INT) RETURNS VOID AS
$$
BEGIN
    BEGIN
        INSERT INTO t1(c1) VALUES (key);
    EXCEPTION WHEN unique_violation THEN
         -- Do nothing, just raise notice
         RAISE NOTICE 'Key % already exists!', key;
    END;
END;
$$
LANGUAGE plpgsql;


BEGIN;
CREATE TABLE t1 (c1 float, unique(c1));
SELECT insertIntoT1(1);
SELECT insertIntoT1(2);
SELECT insertIntoT1(1);
COMMIT;

More info for exception handling and trapping errors in plpgsql http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

score -3 · Answer 2 · answered Jun 18 '14 at 18:19

-3

Just a question: Why you wanna insert the same value two times? Try:

 INSERT IGNORE INTO table VALUES(values);

answered Jun 18 '14 at 18:19

KriszDev

66
1
5

And "INSERT IGNORE" is supposed to do what? – Richard Huxton Jun 18 '14 at 21:50
The script that contains the insert statements is auto-generated and has certain erroneous records as duplicates. So, when I insert, I try to ignore the duplicates. Btw, INSERT IGNORE is not what I want (additionally its for MySQL and not PostgreSQL). I want to ignore duplicates without having to modify the INSERT statements, like the option of setting ON_ERROR_ROLLBACK for the session. But, I'm not exactly sure how to do it from within the python script. – borarak Jun 18 '14 at 22:07
I found this: http://stackoverflow.com/questions/1009584/how-to-emulate-insert-ignore-and-on-duplicate-key-update-sql-merge-with-po . – KriszDev Jun 19 '14 at 05:25
I think inserting into a temporary table without constraints first and then creating an insert rule for inserting into the main table seems to be the only reasonable workaround – borarak Jun 20 '14 at 22:12

Continue executing INSERT script even on error - PostgreSQL using Python script

2 Answers2