让PostgreSQL选择下一个最小可用的id

Question

我想让PostgreSQL选择下一个可用的第一个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是主键。
我该如何告诉PostgreSQL使用下一个可用的id插入记录？

- user4495098

---

你不需要告诉它选择什么，它会自动完成。只需抑制ID信息即可。当然，如果主键是串行的，PostgreSQL将为其创建一个序列。 - Jorge Campos

@Jorge：我认为OP已经在他们的数据库中有了乱序的ID，并想知道如何修复这些问题... - Kevin

你不应该为序列列提供手动值。你觉得你需要这样做吗？ - user330315

我理解，但在我的情景下： 用户可以指定他希望的ID， 或者他不指定ID。 - user4495098

减轻用户控制ID的责任。 - Jorge Campos

我冒昧修改了测试设置。"Test"和Test将会分别指代不同的表。 - Erwin Brandstetter

Answer 1

通常情况下，在“serial”列中最好永远不要覆盖默认设置。如果有时需要手动提供id值，可以将“serial”列的标准DEFAULT子句nextval('sequence_name')替换为一个自定义函数，该函数会省略现有的值。

基于这个虚拟表：

CREATE OR REPLACE FUNCTION f_test_test_id_seq(OUT nextfree bigint)
  LANGUAGE plpgsql 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 FROM test WHERE test_id = val);

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

在默认列中使用它。

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

这不再严格是一个序列，但序列只是一个方便的功能：

• 使用序列主键列安全重命名表

如果您在一个序列列上构建它，那么SEQUENCE将自动“拥有”表列，这可能是一件好事。

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

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

在并发写负载下，它同样安全防止冲突。

表格和序列名称在这里是硬编码的。你可以很容易地将序列名称（就像链接答案中那样）和甚至表格名称参数化，并使用EXECUTE动态语句测试其存在性。这将给你一个通用函数，但调用会稍微昂贵一些。

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

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

在默认列中使用它。

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

小提琴
_舊sqlfiddle

- Erwin Brandstetter