Once I was working with PostgreSQL 9.4, I got an error
of primary key constraint while I was using the sequence that generates the
next serial number. But the sequence implied in the serial data type returned a
number that already exists in the primary key column.
Now the challenge was to set the sequence value
correctly so that it can return the correct number. The simple solutions are
described below.
Reset primary key sequence if you know the name of the sequence
The following query will resolve your problem.
SELECT setval('mytable_id_seq', max(id)) FROM mytable;
|
Here one thing may create a problem like if you don't
know the sequence name then what you will do here.
Reset primary key sequence if you don't know the name of the sequence
Using pg_get_serial_sequence function you can get the
name of the sequence applied on a primary key column. The following query will
help you to reset your primary key sequence.
SELECT setval(pg_get_serial_sequence('mytable', 'id'), max(id)) FROM mytable;
|
Note: PostgreSQL 10 introduced a new feature to generate identity column, if you are using PostgreSQL 10 or above version then not need
to use SERIAL or BIGSERIAL data type to generate an identity column.
No comments:
Post a Comment
Please do not enter any spam link in the comment box.