Auto increment in postgres/sequelize

Question

I have a Postgres database using Sequelize (node/express) as ORM. I have a table called students, in it there are columns: id and name.

In this students table, I have several registered students, but a detail: the last registered ID is 34550 and the first is 30000, they come from an import from a previous database, I need to continue counting from 34550, or that is, from the last registered student. However, when I register a student via API, the generated ID is below 30000. I know that in mysql the ID field being AUTO INCREMENT would solve it, however, as I understand it, postgres works in a different way.

How could I solve this problem?

The migration used to create the table is as follows:

    module.exports = {
    up: (queryInterface, Sequelize) => {
      return queryInterface.createTable('students', {
        id: {
          type: Sequelize.INTEGER,
          allowNull: false,
          autoIncrement: true,
          primaryKey: true,
        },
        name: {
          type: Sequelize.STRING,
          allowNull: false,
        },
      });
    },
  
    down: (queryInterface) => {
      return queryInterface.dropTable('students');
    },
  };

Table print:

Use the function SETVAL() for your sequence: postgresql.org/docs/current/functions-sequence.html — Frank Heikens
– Frank Heikens, Commented Jun 8, 2021 at 15:33

Fred · Accepted Answer · 2021-06-10 17:32:58Z

1

Based on Frank comment, I was able to adjust using:

SELECT setval('public.students_id_seq', 34550, true);

answered Jun 10, 2021 at 17:32

Fred

2322 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dzmitry Dranitski Over a year ago

That sql may not work for all postgre versions. I found my ALTER SEQUENCE "SharedLogs_id_seq" RESTART WITH 111; variant here: stackoverflow.com/a/8750984/10099510

Dzmitry Dranitski · Accepted Answer · 2024-04-10 19:49:37Z

In my case (postgres:15.6) that query helped:

await sequelize.query(`ALTER SEQUENCE "SharedLogs_id_seq" RESTART WITH ${maxId};`);

Here is full migration code:

import {Migration, MigrationParams} from '../../../types/db.migrations';
import {SharedLogRecord} from '../../../types/db';
import fs from 'fs';

type SharedLogRecordFromSqlite = SharedLogRecord & {
  updatedAt: string;
  deletedAt: string;
};

export const up: Migration = async ({context: queryInterface}: MigrationParams) => {
  // 20_shared_logs.json
  const data: SharedLogRecordFromSqlite[] = JSON.parse(
      fs.readFileSync(__dirname + '/20_shared_logs.json', 'utf8'),
  );

  let maxId: number = 0;
  data.forEach((record: SharedLogRecordFromSqlite) => {
    delete record.updatedAt;
    delete record.deletedAt;

    if (record.id > maxId) maxId = record.id;
  });
  maxId++;

  await queryInterface.bulkInsert('SharedLogs', data);
};

export const down: Migration = async () => {};

module.exports = {up, down};

Collectives™ on Stack Overflow

Auto increment in postgres/sequelize

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related