PostgreSQL 序列的下一个值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13393604/
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
PostgreSQL next value of the sequences?
提问by i_nomad
I am using PostgreSQL for my Codeigniter website. I am using grocery crud for add, edit and delete operations. While doing an edit or add, I want to rename an uploaded file dynamically based on the id of the content. I am able to do this using grocery crud's callback_after_uploadfunction.
我在我的 Codeigniter 网站上使用 PostgreSQL。我正在使用杂货杂货进行添加、编辑和删除操作。在进行编辑或添加时，我想根据内容的 id 动态重命名上传的文件。我可以使用杂货杂货的callback_after_upload功能来做到这一点。
I want a next id of the content while adding a new content. I tried to use nextval()function, but sequence gets incremented with it. How can get the last value of the sequence without using nextval()function?
在添加新内容时，我想要内容的下一个 id。我尝试使用nextval()函数，但序列会随之增加。如何在不使用nextval()函数的情况下获取序列的最后一个值？
Or is there a simple way I can do this?
或者有没有一种简单的方法可以做到这一点？
回答by Erwin Brandstetter
RETURNING
RETURNING
Since PostgreSQL 8.2, that's possible with a single round-tripto the database:
从 PostgreSQL 8.2 开始，只需一次往返数据库即可：
INSERT INTO tbl(filename)
VALUES ('my_filename')
RETURNING tbl_id;
tbl_idwould typically be a serialor IDENTITY(Postgres 10 or later) column. More in the manual.
tbl_id通常是 a serialor IDENTITY(Postgres 10 or later) column。手册中有更多内容。
Explicitly fetch value
显式获取值
If filenameneeds to include tbl_id(redundantly), you can still use a single query.
如果filename需要包含tbl_id（冗余），您仍然可以使用单个查询。
Use lastval()or the more specific currval():
INSERT INTO tbl (filename)
VALUES ('my_filename' || currval('tbl_tbl_id_seq') -- or lastval()
RETURNING tbl_id;
See:
看：
If multiple sequences may be advanced in the process (even by way of triggers or other side effects) the sureway is to use currval('tbl_tbl_id_seq').
如果在此过程中可能会推进多个序列（甚至通过触发器或其他副作用），那么肯定的方法是使用currval('tbl_tbl_id_seq').
Name of sequence
序列名称
The string literal'tbl_tbl_id_seq'in my example is supposed to be the actualname of the sequence and is cast to regclass, which raises an exception if no sequence of that name can be found in the current search_path.
我的示例中的字符串文字'tbl_tbl_id_seq'应该是序列的实际名称并被强制转换为regclass，如果在当前search_path.
tbl_tbl_id_seqis the automatically generated default for a table tblwith a serial column tbl_id. But there are no guarantees. A column default can fetch values from anysequence if so defined. And if the default name is taken when creating the table, Postgres picks the next free name according to a simple algorithm.
tbl_tbl_id_seq是为tbl具有串行列的表自动生成的默认值tbl_id。但没有任何保证。如果这样定义，列默认值可以从任何序列中获取值。如果在创建表时采用默认名称，Postgres 会根据一个简单的算法选择下一个空闲名称。
If you don't knowthe name of the sequence for a serialcolumn, use the dedicated function pg_get_serial_sequence(). Can be done on the fly:
如果您不知道serial列的序列名称，请使用专用函数pg_get_serial_sequence()。可以即时完成：
INSERT INTO tbl (filename)
VALUES ('my_filename' || currval(pg_get_serial_sequence('tbl', 'tbl_id'))
RETURNING tbl_id;
回答by a_horse_with_no_name
The previously obtained value of a sequence is accessed with the currval()function.
使用该currval()函数访问先前获得的序列值。
But that will only return a value if nextval()has been called beforethat.
但是如果在此之前nextval()被调用，它只会返回一个值。
There is absolutely no way of "peeking" at the next value of a sequence without actually obtaining it.
绝对没有办法“偷看”序列的下一个值而不实际获得它。
But your question is unclear. If you call nextval()before doing the insert, you can use that value in the insert. Or even better, use currval()in your insert statement:
但是你的问题不清楚。如果nextval()在执行插入之前调用，则可以在插入中使用该值。或者甚至更好，currval()在您的插入语句中使用：
select nextval('my_sequence') ...
... do some stuff with the obtained value
insert into my_table(id, filename)
values (currval('my_sequence'), 'some_valid_filename');
回答by Irlan Cidade
If your are not in a session you can just nextval('you_sequence_name') and it's just fine.
如果您不在会话中，您可以只使用 nextval('you_sequence_name') 就可以了。
回答by Jakob Egger
To answer your question literally, here's how to get the next value of a sequence without incrementing it:
要从字面上回答您的问题，以下是如何在不增加序列的情况下获取序列的下一个值：
SELECT
CASE WHEN is_called THEN
last_value + 1
ELSE
last_value
END
FROM sequence_name
Obviously, it is not a good idea to use this code in practice. There is no guarantee that the next row will really have this ID.However, for debugging purposes it might be interesting to know the value of a sequence without incrementing it, and this is how you can do it.
显然，在实践中使用这段代码并不是一个好主意。不能保证下一行真的有这个 ID。但是，出于调试目的，在不增加序列的情况下知道序列的值可能会很有趣，这就是您可以做到的。
回答by user2699843
I tried this and it works perfectly
我试过这个，它工作得很好
@Entity
public class Shipwreck {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
@Basic(optional = false)
@SequenceGenerator(name = "seq", sequenceName = "shipwreck_seq", allocationSize = 1)
Long id;
....
....
CREATE SEQUENCE public.shipwreck_seq
INCREMENT 1
START 110
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE 1;
回答by radtek
I stumbled upon this question b/c I was trying to find the next sequence value by table. This didn't answer my question however this is how its done, and it may help those looking for the sequence value not by name but by table:
我偶然发现了这个问题 b/c 我试图通过表格找到下一个序列值。这没有回答我的问题，但是这是如何完成的，它可以帮助那些不是按名称而是按表查找序列值的人：
SELECT nextval(pg_get_serial_sequence('<your_table>', 'id')) AS new_id;
Hope it helps :)
希望能帮助到你 ：）
回答by Brian
Even if this can somehow be done it is a terrible idea since it would be possible to get a sequence that then gets used by another record!
即使这可以以某种方式完成，这也是一个糟糕的主意，因为有可能获得一个序列，然后被另一个记录使用！
A much better idea is to save the record and then retrieve the sequence afterwards.
一个更好的主意是保存记录，然后再检索序列。
