Autoincrement attribute based on another attribute

Question

I have a table showtime and a table ticket.

CREATE TABLE showtime (
    showtime_id     SERIAL PRIMARY KEY,
    theatre_id      INT REFERENCES theatre(theatre_id),
    showroom_id     INT REFERENCES showroom(showroom_id),
    movie_id        INT REFERENCES movie(movie_id),
    start_time      TIME,
    end_time        TIME,
    show_date       DATE
);

CREATE TABLE ticket (
    showtime_id     INT REFERENCES showtime(showtime_id),
    seat_number     SERIAL UNIQUE,
    price           NUMERIC(1000, 2) NOT NULL,
    time_bought     TIME,
    date_bought     DATE,
    wasUsed         BOOLEAN,
    PRIMARY KEY     (showtime_id, seat_number)
);

As you can see, each row in showtime will have a unique showtime_id. What I want to happen is when a new ticket is added, the seat_number should auto-increment from the last value tied with showtime_id:

ticket

showtime_id     |   seat_number   |   instead_of   |
      1         |       1         |       1        |
      1         |       2         |       2        |
      1         |       3         |       3        |
      1         |       4         |       4        |
      2         |       1         |       5        |
      2         |       2         |       6        |
      3         |       1         |       7        |
      4         |       1         |       8        |

Is this possible in PostgreSQL?

Answer 1

Rephrasing your question, this is what you want: Whenever you INSERT ticket with a certain showtime_id you want to assign seat_number to the next higher number than already present in ticket for that showtime_id. So what is the highest seat_number already present? Simple:

SELECT max(seat_number)
FROM ticket
WHERE showtime_id = ?; -- The ? being a parameter placeholder in languages like Java

The seat_number data type should not be serial because you will have duplicate seat numbers for different showtimes_ids. Make it a simple integer.

In a single INSERT statement:

INSERT INTO ticket VALUES
  (45,
   (SELECT max(seat_number) FROM ticket WHERE showtime_id = 45) + 1,
   ...
  );

To shield this logic from your users or application, you can use an insert trigger to avoid an erroneous or illegal (PK violation) seat number being entered:

CREATE FUNCTION assign_showtime_seat() RETURNS TRIGGER AS $$
BEGIN
  SELECT max(seat_number) + 1 INTO NEW.seat_number
  FROM ticket
  WHERE showtime_id = NEW.showtime_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_assign_showtime_seat
  BEFORE INSERT ON ticket
  FOR EACH ROW EXECUTE PROCEDURE assign_showtime_seat();

There is a small chance that you encounter a concurrency problem where two tickets for the same showtime_id are entered almost simultaneously. The first insert enters the trigger and computes a new seat_number. Before the INSERT completes the second ticket insert enters the trigger and computes the same seat_number. Either one then manages the actual INSERT and completes, the second ticket entry then fails on insert with a duplicate PRIMARY KEY violation. The documentation provides an example of recovering from such a concurrency problem.

Answer 2

It is possible but either very unreliable for concurrent access or very expensive because you have to lock the whole table to be sure - locking rows will not be enough since you cannot lock rows that have not been entered, yet. (Well, with a separate showtime table, a row lock in the referenced table would help, but that only goes so far ... see comment by @zerkms)

If at all possible stick with the serial column you have now and use a VIEW or MATERIALIZED VIEW to arrive at your numbers:

CREATE MATERIALIZED VIEW v_ticket AS
SELECT *, row_number() OVER (PARTITION BY showtime_id
                             ORDER BY seat_number) AS seat_number_effective
FROM   ticket;

This gives you ascending numbers without gaps per showtime_id.

Very similar:

• Serial numbers per group of rows for compound key