Node Postgres Fix Table IDENTITY seq / autoincrement / nextval

node_postgres_fix_table_identity.js

const pg = require('pg');
const { Client } = pg;

const updateTables = async () => {
  const client = new Client({
    connectionString:
      'postgresql://cysiamadmin@rav-ravhe7-db:[email protected]:5432/postgres?schema=public',
    ssl: {
      rejectUnauthorized: false,
    },
  });

  await client.connect();

  console.log('Connected');

  const execQuery = async (queryStr, params = []) => {
    return new Promise((resolve, reject) => {
      client.query(queryStr, params, (error, results) => {
        if (error) {
          reject(error);
        } else {
          resolve(results);
        }
      });
    });
  };

  const tablesRes = await execQuery(`
    SELECT table_name
    FROM information_schema.tables
    WHERE table_type='BASE TABLE'
    AND table_schema='public';
  `);

  const tables = tablesRes.rows.map((row) => row.table_name);

  await Promise.all(
    tables.map(async (tableName) => {
      let lastId;
      try {
        const res = await execQuery(
          `SELECT id from "${tableName}" ORDER BY id DESC LIMIT 1`,
        );
        lastId = res.rows[0].id;
      } catch (e) {
        console.log(`[Failed] on ${tableName}`);
      }

      if (lastId) {
        const nextId = lastId + 1;
        const queryStr = `ALTER SEQUENCE "${tableName}_id_seq" RESTART WITH ${nextId}`;
        try {
          await execQuery(queryStr);
        } catch (e) {
          console.log(`[Failed] on ${tableName}`);
        }

        console.log(tableName, queryStr);
      }
    }),
  );

  process.exit();
};

updateTables();