How to set up value for serial type in PostgreSQL?

Question

I have a primery key in my table as follows:

CREATE TABLE a (
    id serial NOT NULL,
    valuea citext NOT NULL,
    CONSTRAINT a_pkey PRIMARY KEY (id),
);

Table had the following rows:

id value
198  b
199  j

By accident I did this insert

Insert Into a(id,valuea) values (200,'hello');

Now, when I try to do another insert in the correct way:

Insert Into a(valuea) values ('b');

I expect it to insert (201,b) but the serial counter doesn't know 200 has been used because of the last manual insert.

I get:

ERROR: duplicate key value violates unique constraint "a_pkey" DETAIL: Key (id)=(200) already exists.

I understand this error.. basically it happens because my last insert was not used the Serial and therefore it's counter didn't rise up.

What I don't know is how to fix it?

How do I tell the serial counter to start from 201?

Don't manually maintain your auto increment column. You're not as smart as Postgres. — Tim Biegeleisen, Oct 30 '17 at 09:00

score 3 · Accepted Answer · answered Oct 30 '17 at 08:51

3

You need to find the sequence name, normally something like <your table>_id_seq and do :

ALTER SEQUENCE a_id_seq INCREMENT BY 1;

answered Oct 30 '17 at 08:51

Rémi Desgrange

I know that.. but this is Serial Not actual defined sequence. How do I address it? it doesn't have a name other than the column name. – avi Oct 30 '17 at 08:53
@avi: a `serial` creates a sequence in the background – Oct 30 '17 at 08:53
A serial create a sequence behind the scene. you can list you're sequence by doing : `SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'` – Rémi Desgrange Oct 30 '17 at 08:55
@avi When you create Serial column the sequence define as : `CREATE SEQUENCE tablename_colname_seq;` so Remi gave you the actual answer. – Elad Oct 30 '17 at 08:58

score 3 · Answer 2 · edited Oct 30 '17 at 10:06

3

When you create a serial key, Postgres creates a sequence that it uses to generate next values.

Just find that sequence and modify its START value to e.g 201 .

SELECT setval(<name of the sequence>, 201, true);

edited Oct 30 '17 at 10:06

answered Oct 30 '17 at 08:54

Arnaud

2 Answers2