代码之家 › 专栏 › 技术社区 › Michael Clerx

在带有主键序列的Postgres表上手动插入

sequence primary-key postgresql

Michael Clerx · 技术社区 · 14 年前

我这辈子第一次把一个mysql表转换成postgresql,遇到了传统的新手没有自动增量的问题。

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

我找不到解决的问题是,当您手动将值插入具有唯一或主约束的字段,并将序列的nextvall()作为默认值时会发生什么。据我所见,当序列达到该值时,这会导致插入失败。

有没有一种简单(或常见)的方法来解决这个问题?

一个明确的解释将是非常感谢的。

更新:如果你觉得我不应该这样做,将永远无法解决这个问题,或是做出一些有缺陷的假设,请在你的答案中随意指出。最重要的是,请告诉我该怎么做,以便为程序员提供一个稳定而健壮的数据库,该数据库不能被简单的插入损坏(最好不要隐藏存储过程后面的所有内容)。

5 回复 | 直到 7 年前

Nev Stokes 14 年前

setval()

Chris Pacejo 9 年前

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;

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

Tometzky 14 年前

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');

knitti freethinker 14 年前

id serial NOT NULL CONSTRAINT table_pkey PRIMARY KEY(id) table_id_seq

Bruno Rafael Oliveira 7 年前

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;