incrementing on errors

How do I suppress the 'id' in this table from incrementing when an error occurs?

db=> CREATE TABLE test (id serial primary key, info text, UNIQUE(info));
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "test_info_key" for table "test"
CRE开发者_Python百科ATE TABLE

db=> INSERT INTO test (info) VALUES ('hello') ;
INSERT 0 1

db=> INSERT INTO test (info) VALUES ('hello') ;
ERROR:  duplicate key violates unique constraint "test_info_key"

db=> INSERT INTO test (info) VALUES ('hello') ;
ERROR:  duplicate key violates unique constraint "test_info_key"

db=> INSERT INTO test (info) VALUES ('goodbye') ;
INSERT 0 1

db=> SELECT * from test; SELECT last_value from test_id_seq;

 id |  info   
----+---------
  1 | hello
  4 | goodbye
(2 rows)

 last_value 
------------
          4
(1 row)

---

You cannot suppress this - and there is nothing wrong with having gaps in your ID values.

The primary key is a totally meaningless value that is only used to uniquely identify one row in a table.

You cannot rely on the ID to never have any gaps - just think what happens if you delete a row.

Simply ignore it - nothing is wrong

Edit
Just wanted to mention that this behaviour is also clearly stated in the manual:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back

http://www.postgresql.org/docs/current/static/functions-sequence.html
(Scroll to the bottom)

---

Your question boils down to this: "Can I rollback the next value from a PostgreSQL sequence?"

And the answer is, "You can't." PostgreSQL documentation says

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back . . .

---

Imagine two different transactions go to insert. Transaction A gets id=1 Transaction B gets id=2. Transaction B commits. transaction A rolls back. Now what do we do? How could we roll back the sequence for A without affecting B or later transactions?

---

I figured it out.

I needed to write a wrapper function around my INSERT statement.

The database will normally have one user at a time so the 'race to the next id' condition is rare. What I was concerned about was when my (unmentioned) 'pull rows from remote database table' function would try to reinsert the growing remote database table into the master database table. I am displaying the row ids and I didn't want the users to see the gap in numbering as missing data.

Anyways here is my solution:

CREATE FUNCTION testInsert (test.info%TYPE) RETURNS void AS '
BEGIN
  PERFORM info FROM test WHERE info=$1;
  IF NOT FOUND THEN
    INSERT INTO test (info) VALUES ($1);
  END IF;
END;' LANGUAGE plpgsql;