PostgreSQL 序列的下一个值? [英] PostgreSQL next value of the sequences?

问题描述

我的 Codeigniter 网站使用的是 PostgreSQL.我正在使用杂货杂货进行添加、编辑和删除操作.在进行编辑或添加时，我想根据内容的 id 动态重命名上传的文件.我可以使用杂货杂货的 callback_after_upload 函数来做到这一点.

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_upload function.

在添加新内容时，我想要内容的下一个 id.我尝试使用 nextval() 函数，但序列会随之增加.如何在不使用nextval() 函数的情况下获取序列的最后一个值?

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?

或者有什么简单的方法可以做到这一点?

Or is there a simple way I can do this?

推荐答案

RETURNING

这可以通过单次往返到数据库:

INSERT INTO tbl(filename)
VALUES ('my_filename')
RETURNING tbl_id;

tbl_id 通常是 serial或 IDENTITY(Postgres 10 或更高版本)列.手册中的更多内容.

如果 filename 需要包含 tbl_id(冗余)，您仍然可以使用单个查询.

If filename needs to include tbl_id (redundantly), you can still use a single query.

使用 lastval() 或更具体的 currval():

Use lastval() or the more specific currval():

INSERT INTO tbl (filename)
VALUES ('my_filename' || currval('tbl_tbl_id_seq')   -- or lastval()
RETURNING tbl_id;

见:

• 参考值同一 INSERT 期间另一列中的串行列

如果在过程中可能会推进多个序列(即使是通过触发器或其他副作用的方式)，确定的方法是使用 currval('tbl_tbl_id_seq').

If multiple sequences may be advanced in the process (even by way of triggers or other side effects) the sure way is to use currval('tbl_tbl_id_seq').

字符串文字 'tbl_tbl_id_seq' 在我的例子中应该是序列的实际名称并被转换为 regclass，如果在当前 search_path 中找不到该名称的序列，则会引发异常.

The string literal 'tbl_tbl_id_seq' in my example is supposed to be the actual name 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 是自动生成的默认表 tbl 与串行列 tbl_id.但没有任何保证.如果这样定义，列默认值可以从任何 序列中获取值.如果在创建表时采用默认名称，Postgres 会根据一个简单的算法选择下一个空闲名称.

tbl_tbl_id_seq is the automatically generated default for a table tbl with a serial column tbl_id. But there are no guarantees. A column default can fetch values from any sequence 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.

如果您不知道serial 列的序列名称，请使用专用函数 pg_get_serial_sequence().可以即时完成:

If you don't know the name of the sequence for a serial column, use the dedicated function pg_get_serial_sequence(). Can be done on the fly:

INSERT INTO tbl (filename)
VALUES ('my_filename' || currval(pg_get_serial_sequence('tbl', 'tbl_id'))
RETURNING tbl_id;

db<>fiddle 这里
_旧sqlfiddle

这篇关于PostgreSQL 序列的下一个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！