Skip to content

adding-auto-increment-column (US004)#

Automatic fix is not available.

What it does#

Checks adding of auto increment column.

Why not?#

Adding an auto increment column to an already populated table will have to backfill the newly added column, causing the table to be locked in which no other operations can be performed on the table for the duration of the backfill. This will cause downtime if the table is concurrently being accessed by other clients.

When should you?#

If the table is empty. If the table is not empty but is not being concurrently accessed.

Use instead:#

  1. Create a new column typed bigint, nullable.
  2. Create a sequence.
  3. Set the next value of the sequence to the total number of rows in the table with enough offset.
  4. Set the default value of the new column to the next value of the sequence.
  5. Backfill the new column for all existing rows.
  6. If the new column is to be set as not null, add a check constraint: CHECK (column IS NOT NULL) NOT VALID
  7. Validate the constraint.
  8. Set the column as NOT NULL.
  9. Drop the constraint.