在 PostgreSQL 中获取下一个可用的 auto_increment ID - 更好的方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11008889/
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
Get next available auto_increment ID in PostgreSQL - A better approach?
提问by Jeemusu
I'm new to postgreSQL, so would really appreciate any pointers from the community.
我是 postgreSQL 的新手，所以非常感谢来自社区的任何指点。
I am updating some functionality in the CMS of a pretty old site I've just inherited. Basically, I need the ID of an article before it is inserted into the database. Is there anyway anyway to check the next value that will be used by a sequence before a database session (insert) has begun?
我正在更新我刚刚继承的一个相当老的站点的 CMS 中的一些功能。基本上，在将文章插入数据库之前，我需要文章的 ID。无论如何，在数据库会话（插入）开始之前检查序列将使用的下一个值？
At first I thought I could use SELECT max(id) from tbl_name, however as the id is auto incremented from a sequence and articles are often deleted, it obviously won't return a correct id for the next value in the sequence.
起初我以为我可以使用SELECT max(id) from tbl_name，但是由于 id 从序列中自动递增并且文章经常被删除，它显然不会为序列中的下一个值返回正确的 id。
As the article isn't in the database yet, and a database session hasn't started, it seems I can't use the currval()functionality of postgreSQL. Furthermore if I use nextval()it auto increments the sequence before the data is inserted (the insert also auto-incrementing the sequence ending up with the sequence being doubly incremented).
由于文章还没有进入数据库，而且数据库会话还没有开始，看来我不能使用currval()postgreSQL的功能了。此外，如果我使用nextval()它在插入数据之前自动递增序列（插入也会自动递增序列，最终序列被双递增）。
The way I am getting around it at the moment is as follows:
我目前解决它的方式如下：
function get_next_id()
{
$SQL = "select nextval('table_id_seq')";
$response = $this->db_query($SQL);
$arr = pg_fetch_array($query_response, NULL, PGSQL_ASSOC);
$id = (empty($arr['nextval'])) ? 'NULL' : intval($arr['nextval']);
$new_id = $id-1;
$SQL = "select setval('table_id_seq', {$new_id})";
$this->db_query($SQL);
return $id;
}
I use SELECT nextval('table_id_seq')to get the next ID in the sequence. As this increments the sequence I then immediately use SELECT setval('table_id_seq',$id)to set the sequence back to it's original value. That way when the user submits the data and the code finally hits the INSERT statement, it auto increments and the ID before the insert and after the insert are identical.
我SELECT nextval('table_id_seq')用来获取序列中的下一个 ID。由于这会增加序列，然后我立即用于SELECT setval('table_id_seq',$id)将序列设置回其原始值。这样当用户提交数据并且代码最终命中 INSERT 语句时，它会自动递增并且插入之前和插入之后的 ID 是相同的。
While this works for me, I'm not too hot on postgreSQL and wonder if it could cause any problems down the line, or if their isn't a better method? Is there no way to check the next value of a sequence without auto-incrementing it?
虽然这对我有用，但我对 postgreSQL 并不太感兴趣，想知道它是否会导致任何问题，或者它们是否不是更好的方法？有没有办法在不自动递增的情况下检查序列的下一个值？
If it helps I'm using postgresql 7.2
如果有帮助，我正在使用 postgresql 7.2
采纳答案by a_horse_with_no_name
Disclaimer: Not sure about 7.2 as I have never used that.
免责声明：不确定 7.2，因为我从未使用过它。
Apparently your ID column is defined to get its default value from the sequence (probably because it's defined as serialalthough I don't know if that was available in 7.x).
显然，您的 ID 列被定义为从序列中获取其默认值（可能是因为它被定义为serial虽然我不知道它是否在 7.x 中可用）。
If you remove the default but keep the sequence, then you can retrieve the next ID using nextval()before inserting the new row.
如果删除默认值但保留序列，则可以nextval()在插入新行之前检索下一个 ID 。
Removing the default value for the column will require you to alwaysprovide an ID during insert (by retrieving it from the sequence). If you are doing that anyway, then I don't see a problem. If you want to cater for both scenarios, create a before insert trigger (does 7.x have them?) that checks if the ID column has a value, if not retrieve a new value from the sequence otherwise leave it alone.
删除列的默认值将要求您在插入期间始终提供 ID（通过从序列中检索它）。如果你仍然这样做，那么我看不出有什么问题。如果您想同时满足这两种情况，请创建一个插入前触发器（7.x 是否有它们？）来检查 ID 列是否有值，如果没有从序列中检索新值，否则不要管它。
The real question though is: why do you need the ID before insert. You could simply send the row to the server and then get the generatedid by calling curval()
但真正的问题是：为什么在插入之前需要 ID。您可以简单地将该行发送到服务器，然后通过调用获取生成的idcurval()
But again: you should really (I mean really) talk to the customer to upgrade to a recent version of Postgres
但同样：您真的（我的意思是真的）与客户交谈以升级到最新版本的 Postgres
回答by user1801810
Folks - there are reasons to get the ID beforeinserting a record. For example, I have an application that stores the ID as part of the text that is inserted into another field. There are only two ways to do this.
伙计们 - 有理由在插入记录之前获取 ID 。例如，我有一个应用程序将 ID 作为插入到另一个字段的文本的一部分进行存储。只有两种方法可以做到这一点。
1) Regardless of the method, get the ID before inserting to include in my INSERT statement
2) INSERT, get the the ID (again, regardless of how (SELECT ... or from INSERT ... RETURNING id;)), update the record's text field that includes the ID
1) 不管方法如何，在插入之前获取 ID 以包含在我的 INSERT 语句中
2) INSERT，获取 ID（同样，不管如何 (SELECT ... or from INSERT ... RETURNING id;)），更新包含 ID 的记录文本字段
Many of the comments and answers assumed the OP was doing something wrong... which is... wrong. The OP clearly stated "Basically, I need the ID of an article before it is inserted into the database". It should not matter whythe OP wants/needs to do this - just answer the question.
许多评论和答案都假设 OP 做错了什么……这是……错误的。OP 明确指出“基本上，在将文章插入数据库之前，我需要它的 ID”。为什么OP 想要/需要这样做并不重要- 只要回答这个问题。
My solution opted to get the ID up front; so I do nextval() and setval() as necessary to achieve my needed result.
我的解决方案选择预先获取 ID；所以我根据需要执行 nextval() 和 setval() 来实现我需要的结果。
