SQL 在现有表上创建序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9457799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a sequence on an existing table
提问by djq
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:
如何在表上创建一个序列，使其从 0 -> 最大值？我尝试使用以下 SQL 代码，但它没有在我使用的表中插入任何值：
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.
我试图插入序列的表是另一个查询的输出。我不知道在此初始查询期间添加序列还是在执行查询后将序列添加到表中是否更有意义。
回答by mu is too short
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');
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:
更改现有列的默认值不会更改现有数据，因为数据库无法知道应该更改哪些值；列值上没有“此列具有默认值”标志，只有默认值（最初为 NULL，因为您没有指定任何其他内容）和当前值（也是 NULL），但可以区分“NULL”因为它是默认值”和“NULL 因为它被显式设置为 NULL”。所以，当你分两步做的时候：
- Add column.
- Change default value.
- 添加列。
- 更改默认值。
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.
PostgreSQL 不会将默认值应用于您刚刚添加的列。但是，如果您添加列并同时提供默认值，那么 PostgreSQL 确实知道哪些行具有默认值（所有行），因此它可以在添加列时提供值。
By the way, you probably want a NOT NULL on that column too:
顺便说一句，您可能也希望该列上有一个 NOT NULL：
create sequence rid_seq;
alter table test add column rid integer not null default nextval('rid_seq');
And, as a_horse_with_no_namenotes, if you only intend to use rid_seqfor your test.ridcolumn then you might want to set its owner columnto test.ridso that the sequence will be dropped if the column is removed:
并且，正如a_horse_with_no_name 所指出的，如果您只打算rid_seq用于您的test.rid列，那么您可能希望将其所有者列设置为 ，test.rid以便在删除列时删除序列：
alter sequence rid_seq owned by test.rid;
回答by Robson Rocha
In PostgreSQL:
在 PostgreSQL 中：
UPDATE your_table SET your_column = nextval('your_sequence')
WHERE your_column IS NULL;
回答by Jason 'Bug' Fenter
I'm not fluent in postgresql so I'm not familiar with the "CREATE SEQUENCE" statement. I would think, though, that you're adding the column definition correctly. However, adding the column doesn't automatically insert data for existing rows. A DEFAULT constraint is for new rows. Try adding something like this afterwards to populate data on the existing rows.
我不精通 postgresql，所以我不熟悉“CREATE SEQUENCE”语句。不过，我认为您正确地添加了列定义。但是，添加列不会自动为现有行插入数据。DEFAULT 约束用于新行。之后尝试添加类似的内容以填充现有行上的数据。
DECLARE @i Int
SET @i = 0
SET ROWCOUNT 1
WHILE EXISTS (SELECT 1 FROM test WHERE rid IS NULL) BEGIN
UPDATE test SET rid = @i WHERE rid IS NULL
END
SET ROWCOUNT 0
