CODEWITHSUNDEEP
postgresqlprimary-keypostgresql-9.5
w.k
w.k

Reputation: 8376

Why inserting primary key does not affect sequence?

When inserting rows with implicit primary keys, it seems not affect primary key sequence and then, when trying to insert without PK, it fails:

create table testtable(
  id serial primary key,
  data integer not null
);

Insert with PK (for example on data migration):

insert into testtable ( id, data ) values ( 1,2 ), ( 2,2 ), ( 3,2 ), ( 4,2 );
INSERT 0 4

Inserting new data, without PK:

insert into testtable ( data ) values ( 4 ), ( 5 ), ( 6 ), ( 7 );
ERROR:  duplicate key value violates unique constraint "testtable_pkey"
DETAIL:  Key (id)=(1) already exists.

Why sequence is not set on the max value after first INSERT? Should I control sequences after inserts with PK? Is there way to have sequence automatically on right track?

Upvotes: 0

Views: 576

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246413

The reason for this behavior is that the sequence is accessed in the DEFAULT value of the column, and the default value is not used when the column is inserted explicitly.

The only way to achieve what you want that I can imagine is to have a trigger that modifies the sequence after an insert, but I think that would be a slow and horrible solution.

The best way to proceed would be to adjust the sequence once after you are done with the migration.

Upvotes: 3

Related Questions