How to perform conditional insert based on row count?

I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table:

Table "public.team_joins"
     Column      |           Type           |                            Modifiers                             
-----------------+--------------------------+---------------------------------------------------------
 id              | integer                  | not null default nextval('team_joins_id_seq'::regclass)
 team_id         | integer                  | not null
Indexes:
    "team_joins_pkey" PRIMARY KEY, btree (id)
    "team_joins_team_id" btree (team_id)
Foreign-key constraints:
    "team_id_refs_teams_id" FOREIGN KEY (team_id) REFERENCES teams(id) DEFERRABLE INITIALLY DEFERRED

So, for example, if a team with id 3 only allows 20 players, and SELECT COUNT(*) FROM team_joins WHERE team_id = 3 is equal to 20, no players should be able to join team 3. What's the best way to handle this and avoid concurrency issues? Should I use a SERIALIZABLE transaction to insert, or can I just use a WHERE clause like this in the insert statement?

INSERT INTO team_joins (team_id)
VALUES (3)
WHERE (
  SELECT COUNT(*) FROM team_joins WHERE team_id = 3
) < 20;

Or is there a better option that I'm not considering?

Typically, you have a team table (or similar) with a unique team_id column.

Your FK constraint indicates as much: ... REFERENCES teams(id) - so I'll work with teams(id).

Then, to avoid complications (race conditions or deadlocks) under concurrent write load, it's typically simplest and cheapest to take a write lock on the parent row in team and then, in the same transaction, write the child row(s) in team_joins (INSERT / UPDATE / DELETE).

BEGIN;

SELECT * FROM teams WHERE id = 3 FOR UPDATE;  -- write lock

INSERT INTO team_joins (team_id)
SELECT 3                -- inserting single row
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < 20;

COMMIT;

Example for single row INSERT. To process a whole set at once, you need to do more; see below.

One might suspect a corner case problem in the SELECT. What if there is no row with team_id = 3, yet? Wouldn't the WHERE clause cancel the INSERT?

It wouldn't, because the HAVING clause makes this an aggregation over the whole set which always returns exactly one row (which is eliminated if there are 20 or more for the given team_id already) - exactly the behavior you want. The manual:

If a query contains aggregate function calls, but no GROUP BY clause,
grouping still occurs: the result is a single group row (or perhaps no
rows at all, if the single row is then eliminated by HAVING). The same
is true if it contains a HAVING clause, even without any aggregate
function calls or GROUP BY clause.

Bold emphasis mine.

The case where no parent row is found is no problem either. Your FK constraint enforces referential integrity anyway. If team_id is not in the parent table, the transaction dies with a foreign key violation either way.

All possibly competing write operations on team_joins have to follow the same protocol.

In the UPDATE case, if you change the team_id, you would lock the source and the target team.

Locks are released at the end of the transaction. Detailed explanation in this closely related answer:

• How to atomically replace a subset of table data

In Postgres 9.4 or later, the new, weaker FOR NO KEY UPDATE may be preferable. Also does the job, less blocking, potentially cheaper. The manual:

Behaves similarly to FOR UPDATE, except that the lock acquired is
weaker: this lock will not block SELECT FOR KEY SHARE commands that
attempt to acquire a lock on the same rows. This lock mode is also
acquired by any UPDATE that does not acquire a FOR UPDATE lock.

Another incentive to consider upgrading ...
Insert multiple players of the same team

Usefully assuming you have a column player_id integer NOT NULL. Same locking as above, plus ...

Short syntax:

INSERT INTO team_joins (team_id, player_id)
SELECT 3, unnest('{5,7,66}'::int[])
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < (21 - 3);  -- 3 being the number of rows to insert

The set-returning function in the SELECT list does not comply with standard SQL, but it's perfectly valid in Postgres.

Just don't combine multiple set-returning functions in the SELECT list before Postgres 10, which finally fixed related unexpected behavior.

Cleaner, more verbose, standard SQL doing the same:

INSERT INTO team_joins (team_id, player_id)
SELECT team_id, player_id
FROM  (
   SELECT 3 AS team_id
   FROM   team_joins
   WHERE  team_id = 3
   HAVING count(*) < (21 - 3)
   ) t
CROSS JOIN (
   VALUES (5), (7), (66)
   ) p(player_id);

That's all or nothing. Like in a Blackjack game: one too many and the whole INSERT is out.
Function

To round it off, all of this could conveniently be encapsulated in a VARIADIC PL/pgSQL function:

CREATE OR REPLACE FUNCTION f_add_players(team_id int, VARIADIC player_ids int[])
  RETURNS bool
  LANGUAGE plpgsql AS
$func$
BEGIN
   SELECT * FROM teams WHERE id = 3 FOR UPDATE;         -- lock team
-- SELECT * FROM teams WHERE id = 3 FOR NO KEY UPDATE;  -- in pg 9.4+

   INSERT INTO team_joins (team_id, player_id)
   SELECT $1, unnest($2)                                -- use $1, not team_id
   FROM   team_joins t
   WHERE  t.team_id = $1                                -- table-qualify to disambiguate
   HAVING count(*) < 21 - array_length($2, 1);
   -- HAVING count(*) < 21 - cardinality($2);           -- in pg 9.4+

   RETURN FOUND;                                        -- true if INSERT
END
$func$;

About FOUND.

Call (note the simple syntax with a list of values):

SELECT f_add_players(3, 5, 7, 66);

Or, to pass an actual array - note the VARIADIC key word again:

SELECT f_add_players(3, VARIADIC '{5,7,66}');

Related:

• How to use an array as argument to a VARIADIC function in PostgreSQL?

• Pass multiple values in single parameter

StackExchange Database Administrators Q#167273, answer score: 18