Get next value of big serial and insert

Question

I have the following table, In the present table we have data inserted manually like shown below but now I want to insert latest cola values into table.

create table tbTest
(
    cola bigserial not null primary key,
    colb varchar(10)
);

insert into tbTest(cola,colb) values(1,'A');
insert into tbTest(cola,colb) values(2,'B');
insert into tbTest(cola,colb) values(3,'C');
insert into tbTest(cola,colb) values(4,'D');
insert into tbTest(cola,colb) values(5,'E');
insert into tbTest(cola,colb) values(6,'F');

Now I want insert only column colb values and let serial column calculate next value by self:

Try 1:

insert into tbTest(colb) values('G')

Error: SQL Error [23505]: ERROR: duplicate key value violates unique constraint "tbtest_pkey" Detail: Key (cola)=(1) already exists.

Try 2:

insert into tbTest(cola,colb) values(nextval('tbtest_cola_seq'),'G')

Error: SQL Error [23505]: ERROR: duplicate key value violates unique constraint "tbtest_pkey" Detail: Key (cola)=(2) already exists.

Try 3:

insert into tbTest(cola,colb) values(currval('tbtest_cola_seq')+1,'G');

Error: SQL Error [23505]: ERROR: duplicate key value violates unique constraint "tbtest_pkey" Detail: Key (cola)=(3) already exists.

score 2 · Accepted Answer · answered Aug 20 '20 at 06:10

You have to adjust the sequence to start at a value greater than the maximum existing value.

First

SELECT max(cola) FROM tbtest;

to get the maximum.

Then reset the sequence:

SELECT setval('tbtest_cola_seq', 42);

where 42 is bigger than the value returned by the first query.

Get next value of big serial and insert

1 Answers1