PostgreSQL:在 MAX(the_column)+1 处开始一个序列 [英] PostgreSQL: starting a sequence at MAX(the_column)+1

问题描述

我想将一个序列添加到一个可能已经有数据的列中，所以我试图在已经存在的任何内容之外启动它.假设已经有数据，我想这样做:

I want to add a sequence to a column that might already have data, so I'm trying to start it beyond whatever's already there. Assuming there already is data, I would like to have done it this way:

CREATE SEQUENCE my_sequence MINVALUE 1000000 START
    (SELECT MAX(id_column) FROM my_table) OWNED BY my_table.id_column;

但它一直在 ( 声称语法错误.这就像开始值必须是冷硬数字 - 没有任何象征意义.

but it keeps dying at ( claiming syntax error. It's like the start value has to be cold hard numbers--nothing symbolic.

当然，如果序列足够智能以避免重复值，则更好的解决方案是，因为 id_column 对它有一个独特的约束——这就是我这样做的原因.但据我所知，这是不可能的.

Of course, an even better solution would be if the sequence could be intelligent enough to avoid duplicate values, since id_column has a unique constraint on it--that's why I'm doing this. But from what I can tell, that's not possible.

我也尝试跳过 START 然后做:

I also tried skipping the START and then doing:

ALTER SEQUENCE my_sequence RESTART WITH (SELECT max(id_column)+1 FROM my_table);

但是，同样，它似乎不是象征性的起始值.

but, again, it doesn't seem like to symbolic start values.

我正在运行 PostgreSQL 9.4，但我们的一些客户正在使用与 8.3 一样原始的东西.

I'm running PostgreSQL 9.4 but some of our customers are using stuff as primitive as 8.3.

推荐答案

您不能为起始值指定动态值.

You can't specify a dynamic value for the start value.

但是您可以在创建序列后设置该值:

But you can set the value once the sequence is created:

CREATE SEQUENCE my_sequence MINVALUE 1000000 OWNED BY my_table.id_column;
select setval('my_sequence',  (SELECT MAX(id_column) FROM my_table));

这篇关于PostgreSQL:在 MAX(the_column)+1 处开始一个序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！