PostgreSQL's SERIAL vs MySQL's AUTO_INCREMENT?

Question

I have this snippet of 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');

When I try to write this in 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.

This is because in PostgreSQL, inserting one does not increment the id for the next insert. How can I create my table so that it conforms to the MySQL behaviour?

This is ofcourse an artificial example but I am porting a large code base from MySQL to PostgreSQL and parts of the code (that I don't have control over) uses both styles (i.e with and without ids) and they work in MySQL but does not work in PostgreSQL.

An ugly hack would be to always do SELECT setval('my_table_id_seq', (SELECT count(*) FROM my_table), TRUE) ...

Answer 1

There is no unhacky solution to this: you either stick to SERIAL functionality or handle this by yourself.

But you got me interested, and I've come up with this (little bit less hacky, I hope) solution: create a trigger to do all the dirty job.

Trigger function

(notices added so we can see what's happening):

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;

Set trigger

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

Pulling the trigger

Fast'n'dirty testing

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)

So, now Postgres is handling this case more like you wanted it to, but there're plenty things to check for you: how's that work with bulk inserts, rollbacks, how's this trigger affect performance, and many more fun for you.