在现有表上创建序列 [英] Creating a sequence on an existing table
问题描述
如何在表上创建序列，使其从0到>最大值？
我尝试使用下面的SQL代码，但没有在我正在使用的表中插入任何值：
How can I create a sequence on a table so that it goes from 0 -> Max value? I've tried using the following SQL code, but it does not insert any values into the table that I am using:
CREATE SEQUENCE rid_seq;
ALTER TABLE test ADD COLUMN rid INTEGER;
ALTER TABLE test ALTER COLUMN rid SET DEFAULT nextval('rid_seq');
我要插入序列的表是另一个查询的输出。我不知道在此初始查询期间添加序列还是在执行查询后将序列添加到表是否更有意义。
The table I am trying to insert the sequence in is the output from another query. I can't figure out if it makes more sense to add the sequence during this initial query, or to add the sequence to the table after the query is performed.
推荐答案
在添加新列时设置默认值：
Set the default value when you add the new column:
create sequence rid_seq;
alter table test add column rid integer default nextval('rid_seq');
更改现有列的默认值不会更改现有数据，因为数据库无法知道哪个值应更改；列值上没有此列具有默认值标志，只有默认值（由于未指定其他内容，所以最初为NULL）和当前值（也为NULL），但是可以分辨 NULL之间的区别因为它是默认值和 NULL，因为它已显式设置为NULL。因此，分两个步骤进行操作：
Altering the default value for existing columns does not change existing data because the database has no way of knowing which values should be changed; there is no "this column has the default value" flag on column values, there's just the default value (originally NULL since you didn't specify anything else) and the current value (also NULL) but way to tell the difference between "NULL because it is the default" and "NULL because it was explicitly set to NULL". So, when you do it in two steps:
- 添加列。
- 更改默认值。
PostgreSQL不会将默认值应用于您刚刚添加的列。但是，如果您同时添加该列并提供默认值，则PostgreSQL确实知道哪些行具有默认值（所有行），因此它可以在添加列时提供值。
PostgreSQL won't apply the default value to the column you just added. However, if you add the column and supply the default value at the same time then PostgreSQL does know which rows have the default value (all of them) so it can supply values as the column is added.
顺便说一句，您可能也希望在该列上使用NOT NULL：
By the way, you probably want a NOT NULL on that column too:
create sequence rid_seq;
alter table test add column rid integer not null default nextval('rid_seq');
而且，作为 a_horse_with_no_name 注释，如果您仅打算将 rid_seq 用于 test.rid 列，则可能要设置其所有者列到 test.rid ，这样，如果删除该列，该序列将被删除：
And, as a_horse_with_no_name notes, if you only intend to use rid_seq for your test.rid column then you might want to set its owner column to test.rid so that the sequence will be dropped if the column is removed:
alter sequence rid_seq owned by test.rid;
这篇关于在现有表上创建序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
