Is there a way to set an option that will cause a PostgreSQL script to continue even if there are errors?

Question

Is there a command to set an option that will cause a PostgreSQL script to continue even if there are errors?

eg sometimes when I have a lot of data from a spreadsheet to insert into a table, I use a formula to create INSERT statements, and I copy the statements into a file and execute them, or copy them into a PgAdmin to run them. The accuracy is not always important and I don't want the whole process to fail because of a few records.

Syntax errors are not the issue here, just commands which fail due to some errors, like trying to create an index that already exists, or inserting a duplicate record.

@a_horse_with_no_name Does that apply to any commands at all, not just insert commands? — vfclists
– vfclists, Commented Feb 16, 2013 at 8:53
Yes, that applies to all command (because nearly every statement in Postgres is transactional) — user330315
– user330315, Commented Feb 16, 2013 at 9:10

Daniel Vérité · Accepted Answer · 2013-02-16 12:14:21Z

4

If using psql, you want:

\set ON_ERROR_ROLLBACK on

From the manual:

ON_ERROR_ROLLBACK

When on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues. When interactive, such errors are only ignored in interactive sessions, and not when reading script files. When off (the default), a statement in a transaction block that generates an error aborts the entire transaction

answered Feb 16, 2013 at 12:14

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vyegorov Over a year ago

It works via implicit SAVEPOINTs, so in case there're many statements per transaction, ON_ERROR_ROLLBACK might decrease performance.

Chao Over a year ago

Yes, the implementation of ON_ERROR_ROLLBACK is not smart. We need the Oracle-like statement-level rollback.

user Over a year ago

Is there anything similar when you are querying database from a program?

John L. Stanley · Accepted Answer · 2022-02-23 19:13:16Z

0

A bit late to the party, but I was doing some research on the subject and found an excellent writeup here: Postgres ON_ERROR_ROLLBACK explained

answered Feb 23, 2022 at 19:13

John L. Stanley

711 silver badge6 bronze badges

Collectives™ on Stack Overflow

Is there a way to set an option that will cause a PostgreSQL script to continue even if there are errors?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related