postgresql: Insert two values in table b if both values are not in table a

Question

I'm doing an assignment where I am to make an sql-database of a tournament result. Players can be added by their name, and when the database has at least two or more players who has not already been assigned to a match, two players should be matched against each other.

For instance, if the tables currently are empty I add Joe as a player. I then also add James and since the table then has two players, who also are not in the matches-table, a new row in the matches-table is created with their p_id set to left_player_P_id and right_player_P_id.

I thought it would be a good idea to create a function and a trigger so that every time a row is added to the player-table, the sql-code would run and create the row in the matches as needed. I am open to other ways of doing this.

I've tried multiple different approaches including SQL - Insert if the number of rows is greater than and Using IF ELSE statement based on Count to execute different Insert statements but I am now at a loss.

Problematic code:

This approach returns a syntax error.

IF ((select count(*) from players_not_in_any_matches) >= 2)
  begin
    insert into matches values (
      (select p_id from players_not_in_any_matches limit 1),
      (select p_id from players_not_in_any_matches limit 1 offset 1)
      )
  end;

Alternative approach (still problematic code):

This approach seems more promising (but less readable). However, it inserts even if there are no rows returned inside the where not exists.

insert into matches (left_player_p_id, right_player_p_id)
select
    (select p_id from players_not_in_any_matches limit 1),
    (select p_id from players_not_in_any_matches limit 1 offset 1)
where not exists (
    select * from players_not_in_any_matches offset 2
);

Tables

CREATE TABLE players (
  p_id serial PRIMARY KEY,
  full_name text
  );

CREATE TABLE matches(
  left_player_P_id integer REFERENCES players,
  right_player_P_id integer REFERENCES players,
  winner integer REFERENCES players
  );

Views

-- view for getting all players not currently assigned to a match
create view players_not_in_any_matches as
  select * from players
   where p_id not in (
   select left_player_p_id from matches
   ) and
   p_id not in (
      select right_player_p_id from matches
      );

krokodilko · Accepted Answer · 2017-02-03 18:12:26Z

1

Try:

insert into matches (left_player_p_id, right_player_p_id)
select p1.p_id, p2.p_id
from players p1
join players p2
on p1.p_id <> p2.p_id
and not exists(
   select 1 from matches m
   where p1.p_id in (m.left_player_p_id, m.right_player_p_id)
)
and not exists(
   select 1 from matches m
   where p2.p_id in (m.left_player_p_id, m.right_player_p_id)
)
limit 1

Anti joins (not-exists operators) in the above query could be further simplified a bit using LEFT JOINs:

insert into matches (left_player_p_id, right_player_p_id)
select p1.p_id, p2.p_id
from players p1
join players p2
left join matches m1
on p1.p_id in (m1.left_player_p_id, m1.right_player_p_id)
left join matches m2
on p2.p_id in (m2.left_player_p_id, m2.right_player_p_id)    
where m1.left_player is null
  and m2.left_player is null
limit 1

but in my opinion the former query is more readable, while the latter one looks tricky.

edited Feb 3, 2017 at 18:12

answered Feb 3, 2017 at 17:50

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

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

1 Comment

Roman Tkachuk Over a year ago

Propbably you also need: AND winner IS NULL

Collectives™ on Stack Overflow

postgresql: Insert two values in table b if both values are not in table a

Problematic code:

Alternative approach (still problematic code):

Tables

Views

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problematic code:

Alternative approach (still problematic code):

Tables

Views

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related