3

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.

vfclists
  • 19,193
  • 21
  • 73
  • 92
  • @a_horse_with_no_name Does that apply to any commands at all, not just insert commands? – vfclists Feb 16 '13 at 08:53
  • Yes, that applies to all command (because nearly every statement in Postgres is transactional) –  Feb 16 '13 at 09:10

2 Answers2

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

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • It works via implicit `SAVEPOINT`s, so in case there're many statements per transaction, `ON_ERROR_ROLLBACK` might decrease performance. – vyegorov Feb 16 '13 at 14:43
  • Yes, the implementation of ON_ERROR_ROLLBACK is not smart. We need the Oracle-like statement-level rollback. – Chao Mar 22 '16 at 05:33
  • Is there anything similar when you are querying database from a program? – user Jun 19 '20 at 12:41
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

John L. Stanley
  • 71
  • 1
  • 6