使Postgres选择下一个最小可用ID [英] Make Postgres choose the next minimal available id

问题描述

我想让Postgres选择下一个可用ID ，以便在以下情况下不会出现错误：

I would like to make Postgres choose the first next available id so that no error occurs in the following case:

CREATE TABLE test(
id serial PRIMARY KEY,
name varchar
);

然后：

INSERT INTO test VALUES (2,'dd');
INSERT INTO test (name) VALUES ('aa');
INSERT INTO test (name) VALUES ('bb');

这将给出一个约束错误，因为 id 是主要的。

我怎么能告诉Postgres用下一个空白的ID插入记录？

This will give a constraint error since id is primary.
How can I tell Postgres to insert the record with the next free id?

推荐答案

一般来说，最好不要在 serial 列中排除默认值。如果您有时需要手动提供ID值，请替换标准的 DEFAULT 子句 nextval（'sequence_name'） serial 列具有省略现有值的自定义函数。

Generally it's best to never overrule the default in a serial column. If you sometimes need to provide id values manually, replace the standard DEFAULT clause nextval('sequence_name') of the serial column with a custom function that omits existing values.

基于这个虚拟表：

CREATE TABLE test (test_id serial PRIMARY KEY, test text);

功能：

CREATE OR REPLACE FUNCTION f_test_test_id_seq(OUT nextfree bigint) AS
$func$
BEGIN
LOOP
   SELECT INTO nextfree  val
   FROM   nextval('test_test_id_seq'::regclass) val  -- use actual name of sequence
   WHERE  NOT EXISTS (SELECT 1 FROM test WHERE test_id = val);

   EXIT WHEN FOUND;
END LOOP; 
END
$func$  LANGUAGE plpgsql;

更改默认值：

ALTER TABLE test ALTER COLUMN test_id SET DEFAULT f_test_test_id_seq();

不仅仅是一个 serial 但是 serial 只是一个方便的功能：

It's not strictly a serial any more, but serial is only a convenience feature anyway:

• 安全，干净地重命名使用串行Postgres中的主键列？

• Safely and cleanly rename tables that use serial primary key columns in Postgres?

如果您在 serial 列 SEQUENCE 由表列自动拥有，这可能是一件好事。

And if you build this on top of a serial column the SEQUENCE is automatically "owned" by the table column, which is probably a good thing.

这是一个稍微更快的变体：

This is a slightly faster variant of:

• 自动增量，但省略列中的现有值

• Autoincrement, but omit existing values in the column

表和序列名称是硬编码。您可以轻松地参数化序列名称（如链接的答案），甚至表名称，并使用 EXECUTE 在动态语句中测试存在。会给你一个通用功能，但是这个通话会更贵一些。

Table and sequence name are hard coded here. You could easily parametrize the sequence name (like in the linked answer) and even the table name - and test existence with a dynamic statement using EXECUTE. Would give you a generic function, but the call would be a bit more expensive.

CREATE OR REPLACE FUNCTION f_nextfree(_tbl regclass
                                     , _col text
                                     , _seq regclass
                                     , OUT nextfree bigint) AS
$func$
BEGIN
LOOP
   EXECUTE '
   SELECT val FROM nextval($1) val WHERE NOT EXISTS (
      SELECT 1 FROM ' || _tbl || ' WHERE ' || quote_ident(_col) || ' = val)'
   INTO  nextfree
   USING _seq;

   EXIT WHEN nextfree IS NOT NULL;
END LOOP; 
END
$func$  LANGUAGE plpgsql;

ALTER TABLE test2 ALTER COLUMN test2_id
SET DEFAULT f_nextfree('test2', 'test2_id', 'test2_test2_id_seq');

SQL Fiddle。

这篇关于使Postgres选择下一个最小可用ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！