Let's say we have two tables:
flight segmment
| name | type | | name | type |
|-------------|------| |-------------|------|
| origin | text | | origin | text |
| destination | text | | destination | text |
| flight_id | int |
Where the relationship is One to Many (one flight can have multiple segments).
I would like to know if there is a way to batch insert a lot of rows at the same time while keeping the relationship?
Here's my current method but it may be not optimised: From this documentation here, I'm using this way:
INSERT INTO flight (origin, destination) VALUES
('PAR', 'LON'), -- returning id: 1
('PAR', 'BKK'), -- returning id: 2
('PAR', 'DPS'); -- returning id: 3
I insert multiple flights using above request and I return the id of all of them. Then programmatically I'm updating my segments before inserting them with ids of flight. Lastly I'm inserting every segments using the same methods as flights.
I will end with a second request that seems like:
INSERT INTO segment (origin, destination, flight_id) VALUES
('PAR', 'LON', 1),
('PAR', 'AMS', 2),
('AMS', 'BKK', 2),
('PAR', 'IST', 3),
('IST', 'DPS', 3);
The second problem is that the relationship is one to many so some methods (Multiple INSERTS into one table and many to many table, Insert data in 3 tables at a time using Postgres or How to use RETURNING with ON CONFLICT in PostgreSQL?) seems not to be my use case as I want to batch insert multiple flight AND multiple segments with a relationship one to many.
Is there a way to do this with only one query?