db/post_migrate/20230321170823_backfill_ml_candidates_internal_id.rb


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# frozen_string_literal: true

class BackfillMlCandidatesInternalId < Gitlab::Database::Migration[2.1]
  restrict_gitlab_migration gitlab_schema: :gitlab_main

  def up
    sql = <<-SQL
      UPDATE ml_candidates
      SET internal_id = temp.internal_id_num
      FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY internal_id, id ASC) AS internal_id_num
        FROM ml_candidates
      ) AS temp
      WHERE ml_candidates.id = temp.id
    SQL

    execute(sql)
  end

  def down; end
end