How to ignore rows that violate foreign key constraint in Postgres INSERT INTO?

Question

In this sql statement

INSERT INTO child (parentId, value) SELECT parentId, value FROM temptable

child.parentId is a foreign key pointing to parent.id. Rows in the parent table might be deleted at any time while this long-running INSERT INTO statement (that could insert up to a few million rows) is running.

If the child table has no corresponding foreign key in parent before the INSERT INTO statement begins running, or if a row in the parent table referenced from child was deleted while this INSERT INTO is running, I'd like the INSERT INTO to silently ignore that particular row (and simply skip inserting into child the rows that violate the foreign key constraint) rather than the whole statement failing.

How can I accomplish this? I'm not familiar with how any race conditions would work in this case.

klin · Accepted Answer · 2017-02-20 23:45:43Z

4

You can try to lock referenced rows of the table parent in SELECT:

INSERT INTO child (parent_id, value) 
    SELECT t.parent_id, t.value
    FROM temptable t
    JOIN parent p ON t.parent_id = p.id
    FOR UPDATE OF p;

Note however that this preventive locking may significantly increase the execution time in case when the table parent is intensively modified by other transactions at the same time.

As a remedy you can consider to perform the insertion in several transactions by dividing the data into logical partitions, e.g.

INSERT 
    ...
    WHERE parent_id < 1000

INSERT 
    ...
    WHERE parent_id BETWEEN 1001 AND 2000 -- and so on.

edited Feb 20, 2017 at 23:45

answered Feb 20, 2017 at 23:28

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

How to ignore rows that violate foreign key constraint in Postgres INSERT INTO?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related