database - 让 Postgres 选择下一个最小的可用 id

Question

我想让 Postgres 选择第一个下一个可用的 id 以便在以下情况下不会发生错误:

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 的记录？

Answer 1

通常最好永远不要否决 serial 中的默认值柱子。如果您有时需要手动提供 id 值，请替换标准 DEFAULT条款nextval('sequence_name')的 serial具有省略现有值的自定义函数的列。

基于这个虚拟表:

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无论如何只是一个方便的功能:

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

如果你在 serial 之上构建它专栏SEQUENCE自动被表列“拥有”，这可能是一件好事。

这是一个稍微快一点的变体:

• Autoincrement, but omit existing values in the column

表名和序列名在此处被硬编码。您可以轻松地参数化序列名称(如在链接的答案中)甚至表名称 - 并使用 EXECUTE 通过动态语句测试是否存在.会给你一个通用函数，但调用会有点贵。

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.