can be used to fix the sequences after migrating postgre using AWS DMS for example

setAllSequencesPrismaPostgres.ts

import 'dotenv/config'
import { prismaClient } from '../src/lib/prismaClient'

const setSequenceToMaxInTable = async (props: {
  table_name: string
  sequence_name: string
  column_name: string
}) => {
  const setval = `
  SELECT setval('"${props.sequence_name}"', COALESCE((SELECT MAX(${props.column_name})+1 FROM ${props.table_name}), 1), false);
  `

  const [ret] = (await prismaClient.$queryRawUnsafe(setval)) as {
    setval: number
  }[]

  console.log(`set max for sequence ${props.sequence_name} to ${ret.setval}`)
  // const setOwner = `ALTER SEQUENCE "${props.sequence_name}" OWNER TO official_prod_api;`

  // await prismaClient.$queryRawUnsafe(setOwner)
  // console.log(setOwner)
}

/**
 * this is used for fixing the sequences after a restore from a backup or after migrating to a new database
 */
const setAllSequences = async () => {
  const sequences =
    (await prismaClient.$queryRawUnsafe(`SELECT t.oid::regclass::text AS table_name,
       a.attname AS column_name,
       s.relname AS sequence_name,
       d.deptype, t.relkind, s.relkind
FROM pg_class AS t
   JOIN pg_attribute AS a
      ON a.attrelid = t.oid
   JOIN pg_depend AS d
      ON d.refobjid = t.oid
         AND d.refobjsubid = a.attnum
   JOIN pg_class AS s
      ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
  AND d.refclassid = 'pg_catalog.pg_class'::regclass
  AND d.deptype IN ('a', 'i')
  AND t.relkind IN ('r', 'P')
  AND s.relkind = 'S';`)) as {
      table_name: string
      sequence_name: string
      column_name: string
    }[]
  console.log('~ sequences', sequences)

  for (const sequence of sequences) {
    if (!sequence.table_name.includes('public')) {
      await setSequenceToMaxInTable(sequence)
    }
  }
}

setAllSequences().then(() => {
  process.exit(0)
})