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

Answer 1

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.