Insert from select without losing progress in the middle by Postgresql? "INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)"

Question

Is it possible to move data to the new table sourcing other tables without losing progress in the middle?

I think of plpgsql that read data by chunks and commit after every N inserts... If the process stops in the middle the condition NOT EXISTS (...) allows to add missing data by repeating process again.

I think it is important pattern and there might be support for it directly with the syntax:

INSERT INTO ... SELECT ... WHERE ... AND NOT EXISTS (...)

The reason for copying interruption can be a constraint or user pause an operation. Still we want not to lose progress and resume process (with a help of NOT EXISTS (...) guard).

Answer 1

The reason for copying interruption can be a constraint or user pause an operation.

If it's about the user "pausing" the operation, a PROCEDURE or a DO statement might be the right choice (Postgres 11+). INSERT N rows at a time and COMMIT after each iteration. See:

• Using a commit inside plpgsql block works in one case not another

Picking up after an interruption may be cheaper if the source is stable and you can keep track of an increasing minimum ID or some such.

But if it's just about a constraint that might raise an exception when violated, use an UPSERT query to resolve conflicts: INSERT .. ON CONFLICT .... The conflict action depends on undisclosed details.