How to set up value for serial type in PostgreSQL? [duplicate]

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
– Tim Biegeleisen, Commented Oct 30, 2017 at 9:00

Rémi Desgrange · Accepted Answer · 2017-10-30 08:51:55Z

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, 2017 at 8:51

Rémi Desgrange

9381 gold badge8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

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: a serial creates a sequence in the background

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'

@avi When you create Serial column the sequence define as : CREATE SEQUENCE tablename_colname_seq; so Remi gave you the actual answer.

Arnaud · Accepted Answer · 2017-10-30 10:06:07Z

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

user330315

answered Oct 30, 2017 at 8:54

Arnaud

17.6k3 gold badges34 silver badges46 bronze badges