手动插入具有主键序列的 postgres 表

Question

我一生中第一次将 MySQL 表转换为 PostgreSQL，并遇到了没有 auto_increment 的传统新手问题。

现在我发现postgres的解决方案是使用一个序列，然后在每次插入时请求这个序列的nextval()作为默认值。我还了解到 SERIAL 类型会自动创建序列和主键，并且即使在事务内部调用 nextval() 也会递增计数器以避免锁定序列。

我找不到解决的问题是，当您手动将值插入具有 UNIQUE 或 PRIMARY 约束的字段以及默认序列的 nextval() 时会发生什么情况。据我所知，当序列达到该值时，这会导致 INSERT 失败。

有没有一种简单（或常见）的​​方法来解决这个问题？

非常清楚的解释。

更新：如果您认为我不应该这样做、永远无法解决此问题或做出了一些有缺陷的假设，请随时在您的答案中指出。最重要的是，请告诉我该怎么做才能为程序员提供一个稳定而强大的数据库，该数据库不会因简单的插入而被损坏（最好不要将所有内容隐藏在存储过程后面）

Answer 1

如果您要迁移数据，那么我将删除列上的序列约束，执行所有插入，使用 setval() 将序列设置为数据的最大值，然后恢复列序列 nextval() 默认值。

Answer 2

您可以创建一个触发器来检查 currval('id_sequence_name')>=NEW.id 是否存在。

如果您的交易未使用默认值或 nextval('id_sequence_name')，则 currval 函数将引发错误，因为它仅在当前会话中更新序列时才起作用。如果您使用 nextval 然后尝试插入更大的主键，那么它会抛出另一个错误。然后事务将被中止。

这将防止插入任何会破坏串行的错误主键。

示例代码：

create table test (id serial primary key, value text);

create or replace function test_id_check() returns trigger language plpgsql as
$ begin
  if ( currval('test_id_seq')<NEW.id ) then
    raise exception 'currval(test_id_seq)<id';
  end if;
  return NEW;
end; $;

create trigger test_id_seq_check before insert or update of id on test
  for each row execute procedure test_id_check();

然后使用默认主键插入将工作正常：

insert into test(value) values ('a'),('b'),('c'),('d');

但是插入太大的主键将出错并中止：

insert into test(id, value) values (10,'z');

Answer 3

为了扩展 Tometzky 的精彩答案，这里有一个更通用的版本：

CREATE OR REPLACE FUNCTION check_serial() RETURNS trigger AS $
BEGIN
  IF currval(TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || '_' || TG_ARGV[0] || '_seq') <
    (row_to_json(NEW)->>TG_ARGV[0])::bigint
  THEN RAISE SQLSTATE '55000';  -- same as currval() of uninitialized sequence
  END IF;
  RETURN NULL;
EXCEPTION     
  WHEN SQLSTATE '55000'
  THEN RAISE 'manual entry of serial field %.%.% disallowed',
    TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0]
    USING HINT = 'use DEFAULT instead of specifying value manually',
      SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0];
END;
$ LANGUAGE plpgsql;

您可以将其应用于任何列，例如 test.id，因此：

CREATE CONSTRAINT TRIGGER test_id_check
  AFTER INSERT OR UPDATE OF id ON test
  FOR EACH ROW EXECUTE PROCEDURE check_serial(id);

Answer 4

我不太明白你的问题，但如果你的目标只是进行插入，并有一个有效的字段（例如 id），那么插入没有 id 字段的值，这就是“默认”所代表的意思。它会起作用的。

例如，在表定义中拥有 id serial NOT NULL 和 CONSTRAINT table_pkey PRIMARY KEY(id) 将自动设置 id 并自动递增序列 table_id_seq。

Answer 5

使用 CHECK 怎么样？

CREATE SEQUENCE pk_test
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE test (
    id INT PRIMARY KEY CHECK (id=currval('pk_test')) DEFAULT nextval('pk_test'),
    num int not null
    );
ALTER SEQUENCE pk_test OWNED BY test.id;

-- Testing:
INSERT INTO test (num) VALUES (3) RETURNING id, num;
1,3 -- OK
2,3 -- OK

INSERT INTO test (id, num) values (30,3) RETURNING id, num;
/*
ERROR:  new row for relation "test" violates check constraint "test_id_check"
DETAIL:  Failing row contains (30, 3).

********** Error **********

ERROR: new row for relation "test" violates check constraint "test_id_check"
SQL state: 23514
Detail: Failing row contains (30, 3).
*/

DROP TABLE test;