PostgreSQL: How does COPY FROM behave inside a transaction?

Question

I have an 8-million-line CSV file I need to import into a table. This import will currently take place within a transaction.

Does COPY FROM create a subtransaction? Will a failure roll back the parent transaction, or leave it alone?

Answer 1

Does COPY FROM create a subtransaction?

No. It creates a single transaction if not run in a transaction; otherwise it will use the current transaction.

Will a failure roll back the parent transaction, or leave it alone?

It will roll back the parent transaction.

If you want a subtransaction, start one explicitly before the COPY FROM.

There is no way to cause COPY FROM to ignore errors and continue. Any error causes the whole COPY to fail.