Insert multiple rows across 3 tables in 1 query using returned id

Question

I am recording sports match data in the form of 3 tables:

matches (id, start_time)
match_teams (id, match_id, team_id, score)
match_players (id, match_id, team_id, player_id)

Each match consists of several teams, each team consists of several players (effectively a list of lists). With team_id and player_id as foreign keys on the teams and players table respectively.

Using the above structure, I need to insert into the matches table first and use the returned id to pass into match_teams and match_players.

Following this question I am using the following CTE to accomplish this where I am inserting one match:

WITH a AS (INSERT INTO matches (start_time) 
VALUES ('"0001-01-01T00:00:00+00:00"') 
RETURNING id), 
b AS (INSERT INTO match_teams (match_id, team_id, score) 
VALUES 
((SELECT id FROM a), 5, 1), 
((SELECT id FROM a), 6, 2)) 
INSERT INTO match_players (match_id, team_id, player_id) 
VALUES 
((SELECT id FROM a), 5, 3), 
((SELECT id FROM a), 5, 4),
((SELECT id FROM a), 6, 5)
((SELECT id FROM a), 6, 6);

I want to insert multiple matches at once in one query. I am using offset and limit to select the right match id for the players/ teams.

WITH a AS (INSERT INTO matches (start_time) 
VALUES 
('"0001-01-01T00:00:00+00:00"'),     -- 1st match
('"0001-01-01T00:00:00+00:00"')      -- 2nd match
RETURNING id), 
b AS (INSERT INTO match_teams (match_id, team_id, score) 
VALUES 
((SELECT id FROM a OFFSET 0 LIMIT 1), 5, 1),     -- 1st match
((SELECT id FROM a OFFSET 0 LIMIT 1), 6, 2),     -- 1st match
((SELECT id FROM a OFFSET 1 LIMIT 1), 5, 2),     -- 2nd match
((SELECT id FROM a OFFSET 1 LIMIT 1), 6, 1))     -- 2nd match
INSERT INTO match_players (match_id, team_id, player_id) 
VALUES 
((SELECT id FROM a OFFSET 0 LIMIT 1), 5, 3),     -- 1st match
((SELECT id FROM a OFFSET 0 LIMIT 1), 6, 4),     -- 1st match
((SELECT id FROM a OFFSET 1 LIMIT 1), 5, 5),     -- 2nd match
((SELECT id FROM a OFFSET 1 LIMIT 1), 6, 6);     -- 2nd match

This works, but it seems like a bit of a hacky solution. Is there a best practice way to do this?

Update I've realised I have some redundant columns. I've fixed this up, but I don't think it changes the question significantly. My question is more like "is using offset and limit like this best practice?"

You could use sequences (nextval, currval) to avoid those `selects` with `offset`. Although I'm pretty sure that this one query solution is not a best pratice at all. — Jorge Campos, Nov 03 '15 at 13:43

klin · Accepted Answer · 2015-11-03T21:55:37.603

Is using offset and limit like this best practice?

Certainly not. This is really inefficient and very inconvenient to use, especially for a larger number of matches.

You can use row_number() to choose proper id to join teams and players with teams:

with teams (rn, team_ids, scores) as (
    values 
        (1, array[5, 6], array[1, 2]),  -- match #1 in this query
        (2, array[5, 6], array[2, 1])   -- match #2 in this query
    ),
players (rn, team_ids, player_ids) as (
    values 
        (1, array[5, 5, 6, 6], array[3, 4, 5, 6]),
        (2, array[5, 5, 6, 6], array[3, 4, 5, 6])
    ),
ins_matches as (
    insert into matches (start_time) 
    values 
        ('"0001-01-01t00:00:00+00:00"'),
        ('"0001-01-01t00:00:00+00:00"')
    returning id
    ),
matches as (
    select id, row_number() over (order by id) rn
    from ins_matches        -- rn - number of match in this query
    ),
ins_teams as (
    insert into match_teams (match_id, team_id, score) 
    select id, unnest(team_ids), unnest(scores)
    from matches
    join teams using(rn)
    ) 
insert into match_players (match_id, team_id, player_id) 
select id, unnest(team_ids), unnest(player_ids)
from matches
join players using(rn);

score 0 · Answer 2 · answered Nov 03 '15 at 14:10

In my case I have route_sources and routes

route_sources have route_source_id as PK
routes have route_source_id as FK

.

-- GOT ID TO ROUTE_SOURCES FROM SEQUENCE
int_route_source_id = nextval('traffic.route_sources_route_source_id_seq'::regclass);

-- CREATE NEW RECORD FOR ROUTE_SOURCES
INSERT INTO traffic.Route_Sources 
    (route_source_id, sql, ini_avl_id, ini_link_id)
VALUES
    (int_route_source_id, strSQL, A.avl_id, A.link_id, ini_offset); 

-- CREATE THE ROUTE
INSERT INTO traffic.Routes 
    (route_source_id, seq_id, node_id, link_id, cost)               
SELECT int_route_source_id, seq, id1 AS node, id2 AS edge, cost::numeric(11,4)
    FROM pgr_trsp;

Insert multiple rows across 3 tables in 1 query using returned id

2 Answers2