Postgresql 序列
当我从 Postgresql 表中删除所有记录，然后尝试重置序列以在插入时以编号 1 开始新记录时，我得到不同的结果：
SELECT setval('tblname_id_seq', (SELECT COALESCE(MAX(id),1) FROM tblname));
这将序列的当前值设置为 1，但下一条记录 (实际上是第一个，因为还没有记录）获得数字 2！
而且我不能将其设置为0，因为序列中的最小值是1！
当我使用：
ALTER SEQUENCE tblname_id_seq RESTART WITH 1;
插入的第一条记录实际上得到数字1！但是上面的代码不接受 SELECT 作为值而不是 1。
我希望在没有记录时将序列重置为数字 1，并且第一条记录应该以 1 开头。但是当表，我想重置序列，以便插入的下一条记录将获得 {highest}+1
有人对此有明确的解决方案吗？
When I delete all records from a Postgresql table and then try to reset the sequence to start a new record with number 1 when it is inserted, i get different results :
SELECT setval('tblname_id_seq', (SELECT COALESCE(MAX(id),1) FROM tblname));
This sets the current value of the sequence to 1, but the NEXT record (actually the first because there are no records yet) gets number 2!
And I can't set it to 0, because the minimum value in the sequence is 1!
When I use :
ALTER SEQUENCE tblname_id_seq RESTART WITH 1;
the first record that is inserted actually gets number 1 ! But the above code doesn't accept a SELECT as a value instead of 1.
I wish to reset the sequence to number 1 when there are no records, and the first record then should start with 1. But when there ARE already records in the table, I want to reset the sequence so that the next record that is inserted will get {highest}+1
Does anyone have a clear solution for this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
使用
setval的三参数形式将is_called标志设置为 false，以便它返回下一个nextval序列的当前值> 调用而不是立即生成一个新的。http://www.postgresql.org/docs/current/interactive/functions -sequence.html
另请注意，您需要使用
COALESCE(MAX(id),0)+1，否则序列中的第一个值将为MAX(id)< /code>，您知道它已经存在。 （谢谢斯蒂芬·丹尼）Use the three-argument form of
setvalto set theis_calledflag to false, so that it returns the current value of the sequence for the nextnextvalcall rather than immediately generating a new one.http://www.postgresql.org/docs/current/interactive/functions-sequence.html
Also note you need to use
COALESCE(MAX(id),0)+1, otherwise the first value from the sequence will beMAX(id), which you know to already exist. (thx Stephen Denne)请参阅http://www.postgresql.org/docs/current/static/ functions-sequence.html，靠近页面底部。
具体来说，至少在Postgresql 9.0中，您可以查找并设置序列的值。您可以使用
setval()的三参数形式来设置序列的当前值或序列的下一个值（这将允许您设置在下一个值检索时序列为 1）。See http://www.postgresql.org/docs/current/static/functions-sequence.html, near the bottom of the page.
Specifically, at least in Postgresql 9.0, you can find and set the value of a sequence. You can use the three-argument form of
setval()to set either the current value of the sequence or the next value of the sequence (which would allow you to set the sequence to 1 on the next value retrieval).