CODEWITHSUNDEEP
sqlpostgresql
nomadoda
nomadoda

Reputation: 4942

Postgres: Insert values in two tables simultaneously from csv

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?

Upvotes: 2

Views: 173

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246473

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.

Upvotes: 2

Related Questions