Postgres complains id 'already exists' after insert of initial data?

Question

I have an issue where Postgres is complaining of a duplicate ID following an import of some initial data and I am trying to see how to increment the id column counter?

Details:

I have recently uploaded some initial data into a Postgres table, where the id is set to autoincrement in my Sequelize model definition. For example:

    sequelize.define('user', {
        id: {
            type: Sequelize.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        name: Sequelize.STRING
    }

The data insert looks like:

INSERT INTO "users" ("id","name") VALUES(1, "bob");
INSERT INTO "users" ("id","name") VALUES(2, "brooke");
INSERT INTO "users" ("id","name") VALUES(3, "nico");

Now from my node.js application when I try to insert a new record it complains that Key (id)=(1) already exists. The SQL Sequelize is using is of the form:

INSERT INTO "users" ("id","name") VALUES(DEFAULT, "nico");

If I am empty the table of records and try this again or retry the operations enough times, then I see the counter does increment. The issue seems Postgres is not able to tell what the current max id is, based on the records?

What would be the right way to tell Postgres to update the counters, following uploading initial data into the database?

BTW using Postgres 9.6

[Does Postgresql SERIAL work differently?](https://stackoverflow.com/a/18389891/1995738) — klin, Jun 22 '17 at 20:28
See also [Serial Types](https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL) — klin, Jun 22 '17 at 20:30
Behaviour described in linked article matches what we see here. That is manually specifying the id value won't update the sequence. Only `DEFAULT` increments the value, so it appears important to refresh the sequence. This is different to MySQL, for example, which increments automatically based on the largest value in the column. — Andre M, Jun 23 '17 at 20:57

score 30 · Accepted Answer · answered Jun 22 '17 at 20:32

30

After a bit more searching it turns out this will do what I need to do.

SELECT setval('users_id_seq', max(id)) FROM users;

This code will set the id to the current maximum id in the table, here it being my users table. Note, to check if a sequence is associated with a column, this will work:

SELECT pg_get_serial_sequence('patients', 'id')

The only thing to note is that you ignore the 'public.' part in the returned value.

I'll add the setval() to my initial data script.

answered Jun 22 '17 at 20:32

Andre M

6,649
7
52
93

I get "`users_id_seq` does not exist" – étale-cohomology Sep 10 '21 at 01:01
@étale-cohomology, I assume my response is probably quite late =) However, please note that: **users_id_seq** where `users` is a table name, `id` column in that table. You might have different name for your table and column. – Eugene Jan 23 '23 at 16:14

score 0 · Answer 2 · answered Jun 22 '17 at 20:15

0

Try dropping the table before you initially insert data, it may be persisting from a previous run in which case (1, "bob") would already be in your table before you tried adding it again.

answered Jun 22 '17 at 20:15

littlespice3

131
1
2
12

The issue is not during the initial add, but during normal operation afterwards. All the values with specified ids added correctly, but not the one with the `DEFAULT` id. Simulating this from the psql command line suggests that the inserting with ids is not updating the incrementor for the column. – Andre M Jun 22 '17 at 20:24
I see, my mistake. I'm not entirely familiar enough with postgres to comment on how to keep track of the next id value that needs to be autogenerated – littlespice3 Jun 22 '17 at 20:30

score 0 · Answer 3 · answered Apr 21 '21 at 14:45

This happened to me because I inserted records using literal, numeric values (instead of DEFAULT or undefined) as arguments for the auto-incremented column. Doing so circumvents the column's underlying sequence object's increment call, hence making the sequence's value out of sync with the values in the column in the table.

score 0 · Answer 4 · answered Aug 24 '21 at 07:10

0

SELECT setval('users_id_seq', (SELECT MAX(id) from users));

The name of the sequence is auto generated and is always tablename_columnname_seq.

answered Aug 24 '21 at 07:10

Usman

949
9
12

Postgres complains id 'already exists' after insert of initial data?

4 Answers4