sql - 在 PostgreSQL 事务 block 中获取 ID

Question

我正在尝试将所有应该是全有或全无的事务包装到 BEGIN 和 COMMIT 中，但我不确定如何在像下面这样的情况。

我有3张表，一张是图片，一张是albums，还有一张是他们之间的关系，即album_images。该系统的工作方式是用户可以创建一个相册并在一个操作中用他的图像填充它。 SQL如下:

BEGIN;
  INSERT INTO albums [...];  -- Create a new album row
  SELECT id AS album_id FROM albums WHERE [...];  -- Get that rows ID
  -- Now use album_id in the next statement
  INSERT INTO album_images (album_id, image_id) [...];
COMMIT;

这可能是一个常见问题，我只是不确定要搜索什么，而且我似乎也无法在文档中找到解决方案。

Answer 1

作为 Cody 提到的 INSERT ... RETURNING 子句的替代方案，您可以使用与 ID 列关联的序列的当前值:

BEGIN;
  INSERT INTO albums [...];
  INSERT INTO album_images (currval('albums_id_seq'), image_id) [...];
COMMIT;

当为定义为 serial 的列自动创建序列时，这假定了 Postgres 的标准命名方案。

另一种选择 - 如果您只使用单个插入 - 是使用 lastval() 函数。然后您甚至不需要将序列名称放入 INSERT 语句中:

BEGIN;
  INSERT INTO albums [...];
  INSERT INTO album_images (lastval(), image_id) [...];
COMMIT;