如何使用 PostgreSQL 序列更新 SQL 中的唯一值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/148005/
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
How to update unique values in SQL using a PostgreSQL sequence?
提问by Peter Hilton
In SQL, how do update a table, setting a column to a different value for each row?
在 SQL 中，如何更新表，将每一行的列设置为不同的值？
I want to update some rows in a PostgreSQL database, setting one column to a number from a sequence, where that column has a unique constraint. I hoped that I could just use:
我想更新 PostgreSQL 数据库中的一些行，将一列设置为序列中的一个数字，其中该列具有唯一约束。我希望我可以使用：
update person set unique_number = (select nextval('number_sequence') );
but it seems that nextvalis only called once, so the update uses the same number for every row, and I get a 'duplicate key violates unique constraint' error. What should I do instead?
但似乎nextval只被调用一次，因此更新对每一行使用相同的数字，并且我收到“重复键违反唯一约束”错误。我应该怎么做？
回答by Grey Panther
Don't use a subselect, rather use the nextval function directly, like this:
不要使用子选择，而是直接使用 nextval 函数，如下所示：
update person set unique_number = nextval('number_sequence');
回答by TravisO
I consider pg's sequences a hack and signs that incremental integers aren't the best way to key rows. Although pgsql didn't get native support for UUIDs until 8.3
我认为 pg 的序列是一个黑客，并表明增量整数不是关键行的最佳方式。尽管 pgsql 直到 8.3 才获得对 UUID 的原生支持
http://www.postgresql.org/docs/8.3/interactive/datatype-uuid.html
http://www.postgresql.org/docs/8.3/interactive/datatype-uuid.html
The benefits of UUID is that the combination are nearly infinite, unlike a random number which will hit a collision one day.
UUID 的好处是组合几乎是无限的，不像随机数会在某一天发生碰撞。
