Will a Postgres Serial Primary Key overwrite an existing primary key?

Question

I just created a new database and copied all of the data from another database into the new database. In one of the database tables titled 'users', there is a column titled 'user_id' that I have set to 'serial primary key', so it auto increments.

I just noticed that the auto increment number for new users reset to 1 after the database change.

Currently, the lowest user_id number is 13 and the highest is 568. Before changing to the new database, the auto increment would continue past the highest number in the database. But now in the new database, it started the auto increment back at 1.

My question is, when the auto increment reaches 13, will postgres automatically jump past the number 13 to the next available number that is currently not in use? Or will the auto increment use the number 13 and I will have two users with the same user_id 13?

https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync?r=SearchResults&s=1|268.2230 — , Dec 15 '21 at 17:19

score 1 · Accepted Answer · answered Dec 15 '21 at 17:29

The sequence value will not skip 13, and the INSERT will fail with a primary key violation.

After loading the data, you should set the sequence counter to a value higher than the maximum actual primary key value in the table.

Find out which sequence is used:
```
SELECT pg_get_serial_sequence('users', 'user_id');
```
Let's assume the result is users_user_id_seq.
Find the maximum in the table:
```
SELECT max(user_id) FROM users;
```

Set the sequence to a higher value:

SELECT setval('users_user_id_seq', 1000000);

Thank you so much!!! You just saved my app from getting totally messed up — Page COW, Dec 15 '21 at 17:34

Will a Postgres Serial Primary Key overwrite an existing primary key?

1 Answers1