PostgreSQL的SERIAL与MySQL的AUTO_INCREMENT有什么区别？

Question

我有这段MySQL代码：

CREATE TABLE seq_test (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name TEXT
);

INSERT INTO seq_test(id, name) VALUES (1, 'one');
INSERT INTO seq_test(name) VALUES ('two');

当我尝试在PostgreSQL中编写以下内容时：

CREATE TABLE seq_test (
  id SERIAL PRIMARY KEY,
  name TEXT
);

INSERT INTO seq_test(id, name) VALUES (1, 'one');
INSERT INTO seq_test(name) VALUES ('two');

I get the following error:

[23505] ERROR: duplicate key value violates unique constraint "seq_test_pkey"
  Detail: Key (id)=(1) already exists.

这是因为在PostgreSQL中，插入一条记录并不会为下一个插入操作自动增加id。那么我该如何创建我的表格使其符合MySQL的行为呢？

当然，这只是一个人工案例，我正在将一个大型代码库从MySQL迁移到PostgreSQL，而代码的某些部分（我无法控制）同时使用了两种风格（即带有和不带有ids），在MySQL中可以正常工作但在PostgreSQL中却不行。

一个丑陋的hack方法是始终执行SELECT setval('my_table_id_seq', (SELECT count(*) FROM my_table), TRUE)...

- pathikrit

---

@muistooshort：不幸的是，我无法对提供ID的代码做出太多改变。无论如何，实际情况放在一边，让PostgreSQL在这种情况下像MySQL一样运行的最简单方法是什么？ - pathikrit

Answer 1

这个问题没有绝对的解决方案：你要么坚持使用序列功能，要么自己处理。

但是你引起了我的兴趣，我想到了一个（希望不太hacky的）解决方法：创建触发器来完成所有的脏活。

触发器函数

（添加通知以便我们看到发生了什么）：

CREATE OR REPLACE FUNCTION update_seq_val_seq_test()
  RETURNS TRIGGER AS $$
BEGIN
  RAISE NOTICE 'id is %', NEW.id;
  IF NEW.id > currval('seq_test_id_seq' :: REGCLASS)
  THEN
    RAISE NOTICE 'curval is %', currval('seq_test_id_seq' :: REGCLASS);
    PERFORM setval('seq_test_id_seq' :: REGCLASS, (NEW.id) :: BIGINT);
    RAISE NOTICE 'new curval is %', currval('seq_test_id_seq' :: REGCLASS); END IF;
  RETURN NULL;
END;
$$
LANGUAGE 'plpgsql' COST 1;

设置触发器

CREATE TRIGGER seq_test_update_serial
  AFTER INSERT ON seq_test
  FOR EACH ROW EXECUTE PROCEDURE update_seq_val_seq_test();

扣动扳机

快速而简单的测试

tests2=# insert into seq_test (name) values ('first');
NOTICE:  id is 30
INSERT 0 1
tests2=# select * from seq_test;
 id | name  
----+-------
 30 | first
(1 row)

tests2=# select currval('seq_test_id_seq'::regclass);
 currval 
---------
      30
(1 row)

tests2=# insert into seq_test (id, name) values (31, 'thirty one');
NOTICE:  id is 31
NOTICE:  curval is 30
NOTICE:  new curval is 31
INSERT 0 1
tests2=# select currval('seq_test_id_seq'::regclass);
 currval 
---------
      31
(1 row)

tests2=# select * from seq_test;
 id |    name    
----+------------
 30 | first
 31 | thirty one
(2 rows)

tests2=# insert into seq_test (name) values ('thirty dunno what');
NOTICE:  id is 32
INSERT 0 1
tests2=# insert into seq_test (id, name) values (21, 'back to the future');
NOTICE:  id is 21
INSERT 0 1
tests2=# select currval('seq_test_id_seq'::regclass);
 currval 
---------
      32
(1 row)

tests2=# select * from seq_test;
 id |        name        
----+--------------------
 30 | first
 31 | thirty one
 32 | thirty dunno what
 21 | back to the future
(4 rows)

tests2=# insert into seq_test (name) values ('thirty dunno what++');
NOTICE:  id is 33
INSERT 0 1
tests2=# select * from seq_test;
 id |        name         
----+---------------------
 30 | first
 31 | thirty one
 32 | thirty dunno what
 21 | back to the future
 33 | thirty dunno what++
(5 rows)

现在Postgres处理这种情况更像您想要的方式，但有很多需要检查的事情：它如何处理批量插入、回滚，这个触发器对性能有什么影响，以及其他许多有趣的问题等待您去解决。

- Evgeniy Chekan