1

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!

2 Answers 2

2

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

-3

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

 INSERT IGNORE INTO table VALUES(values);
4
  • And "INSERT IGNORE" is supposed to do what? Commented Jun 18, 2014 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. Commented Jun 18, 2014 at 22:07
  • I found this: stackoverflow.com/questions/1009584/… . Commented Jun 19, 2014 at 5: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 Commented Jun 20, 2014 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.