如何使用查询的起始值创建序列? [英] How to create sequence using starting value from query?

问题描述

在数据库迁移脚本(psql)中，我想从表中查询的某个值开始创建序列，例如:

In database migration script (psql) I want to create sequence starting from certain value queried from table, like:

CREATE SEQUENCE book_id_seq START ( SELECT MAX(id) + 1 FROM book.book );

或尝试设置 \set start (SELECT MAX(id) + 1 FROM book.book) 以使用如下变量:

or tried to set \set start (SELECT MAX(id) + 1 FROM book.book) to use variable like:

CREATE SEQUENCE book_id_seq START :'start';

但使用 \set 并不能解释查询.

But using \set did not inerpret the query.

另一种设置变量的方法也不起作用

Another way setting variable did not work also

start := SELECT MAX(id) + 1 FROM book.book;

报错:

ERROR:  syntax error at or near ":="
LINE 1: start := SELECT MAX(id) + 1 FROM book.book;

选择值 INTO 也无济于事，因为访问需要另一个 SELECT 查询.

Selecting value INTO does not help also, because accessing needs another SELECT query.

使用静态值工作正常:

\set start 33
CREATE SEQUENCE book_id_seq START :'start'::int;

如何使用动态起始值?

Postgres 9.6

Postgres 9.6

推荐答案

你可以使用 setval() 创建序列后:

you can use setval() after you created the sequence:

CREATE SEQUENCE book_id_seq;
select setval('book_id_seq', (SELECT MAX(id) + 1 FROM book.book));

这篇关于如何使用查询的起始值创建序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！