Skip to content

not-null-constraint-on-new-column-with-volatile-default (US011)#

Automatic fix is not available.

What it does#

Checks NOT NULL constraint on a new column with volatile default.

Why not?#

Adding a new column with NOT NULL constraint and a volatile default to an already populated table will have to backfill the newly added column with the default, 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 the new column, nullable.
  2. Set the default value of the new column to the volatile default.
  3. Backfill the new column for all existing rows.
  4. Create a check constraint: CHECK (column IS NOT NULL) NOT VALID.
  5. Validate the constraint.
  6. Set the column as NOT NULL.
  7. Drop the constraint.