postgresql - 基于另一个属性的自动增量属性

Question

我有一张 table showtime 和一张 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)
);

如您所见，showtime 中的每一行都有一个唯一的 showtime_id。我想要发生的是当添加新的 ticket 时，seat_number 应该从与 showtime_id 绑定(bind)的最后一个值自动递增:

门票

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        |

这在 PostgreSQL 中可能吗？

Answer 1

重新表述您的问题，这就是您想要的:每当您使用某个 showtime_id INSERT ticket 时，您想要将 seat_number 分配给下一个比 showtime_id 的 ticket 中已有的数字更高。那么已经存在的最高 seat_number 是多少？简单:

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

seat_number 数据类型不应为serial，因为不同的showtimes_id 会有重复的座位号。使它成为一个简单的整数。

在单个 INSERT 语句中:

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

为了对您的用户或应用程序屏蔽此逻辑，您可以使用插入触发器来避免输入错误或非法(违反 PK)的座位号:

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

您遇到并发问题的可能性很小，即几乎同时输入同一 showtime_id 的两张票。第一个插入进入触发器并计算一个新的 seat_number。在 INSERT 完成之前，第二张票插入进入触发器并计算相同的 seat_number。然后其中一个管理实际的 INSERT 并完成，第二个票证条目随后在插入时因重复的 PRIMARY KEY 违规而失败。文档提供了an example从这样的并发问题中恢复。