Postgres: Insert values in two tables simultaneously from csv

Question

I have two tables with each a foreign key referencing the other:

users
favorite_post_id int NOT NULL

posts
user_id int NOT NULL

Consider a user can have many posts, but only a single favorite post.

I want to seed my database with two CSV files using the COPY command. The data contain rows that reference each other (i.e. a post with (id: 2, user_id: 1), and a user with (id: 1, favorite_post_id: 2))

However I'm unable to get the insertion to happen simultaneously, resulting in an error of inserting to one table violating the foreign constraint to the other.

insert or update on table "posts" violates foreign key constraint "FK_d8feca7198a0931f8234dcc58d7"

Key (user_id)=(1) is not present in table "users".

Is there a way to commit the insertion at once, so that it happens simultaneously?

Disable the foreign constraints and re-enable them after you have loaded both tabes. — Gordon Linoff
– Gordon Linoff, Commented Aug 23, 2019 at 14:50
If the user table uniquely identifies each user, then this design will allow only a single post per user. Is that your intent? A design that allows multiple posts per user would have a FK from posts into users, but not vice-versa--and would not create such foreign key errors when loading data. — rd_nielsen
– rd_nielsen, Commented Aug 23, 2019 at 14:52
@rd_nielsen True, a bit of a poor example. Consider a user can only have one favorite_post_id, but many posts. — nomadoda
– nomadoda, Commented Aug 23, 2019 at 14:54
If a user has no favorite post, then presumably users.post_id is nullable. In that case, load the data without setting users.post_id and then update users to set post_id after the posts table is loaded. — rd_nielsen
– rd_nielsen, Commented Aug 23, 2019 at 14:59
@rd_nielsen That would make sense, but favorite_post_id is not nullable in the actual scenario. Consider the user has to mark a favorite post upon registering. A bit silly, I should definitely consider a better example. — nomadoda
– nomadoda, Commented Aug 23, 2019 at 15:03

Laurenz Albe · Accepted Answer · 2019-08-26 09:09:31Z

2

If you are certain that the imports won't cause inconsistencies, you can

BEGIN;

SET LOCAL session_replication_role = replica;

COPY users FROM ...;
COPY posts FROM ...;

COMMIT;

This setting will disable triggers and hence foreign key constraints.

If you are not 100% certain of the data, the better way is to drop one of the foreign key constraints, load the tables and create the constraint again.

You can do that in a transaction as well if you need to prevent concurrent activity from creating inconsistencies.

edited Aug 26, 2019 at 9:09

answered Aug 23, 2019 at 14:58

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

nomadoda Over a year ago

I also needed to reset session_replication_role to default after committing: SET session_replication_role = origin;

Laurenz Albe Over a year ago

Yes. Or you use SET LOCAL as in my updated answer.

Collectives™ on Stack Overflow

Postgres: Insert values in two tables simultaneously from csv

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related