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
– Jorge Campos, Commented Nov 3, 2015 at 13:43

klin · Accepted Answer · 2015-11-03 21:55:37Z

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);

Juan Carlos Oropeza · Accepted Answer · 2015-11-03 14:10:10Z

0

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;

answered Nov 3, 2015 at 14:10

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related