> ## Documentation Index
> Fetch the complete documentation index at: https://www.pgschema.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom Migration Steps

pgschema generates the plan from the diff between your schema files and the database, but the diff engine cannot infer everything. The most common case is a schema change that requires touching existing user data, such as backfilling a new column. Declare the end state in your schema file, then edit the generated plan to add the steps by hand.

<Note>
  Rows of config tables (lookups, settings, feature flags) do not need this: list them in [`pgschema.toml`](/cli/config), keep their rows in CSV files loaded by [`\copy`](/syntax/copy), and pgschema plans the row changes for you. See [Config Data](/workflow/config-data). This page covers one-off changes to data that pgschema does not own, and any other step the diff cannot produce.
</Note>

This works because the plan's `source_fingerprint` pins the **database state** the plan was generated against, not the plan's SQL. You can freely edit the steps; `pgschema apply` still detects if the database has drifted since planning.

## Example: add a NOT NULL column with backfill

Desired state in `schema.sql`:

```sql theme={null}
CREATE TABLE users (
    id bigint PRIMARY KEY,
    email text NOT NULL,
    email_domain text NOT NULL
);
```

<Steps>
  <Step title="Generate Plan">
    ```bash theme={null}
    pgschema plan --host localhost --db myapp --user postgres \
      --file schema.sql --output-json plan.json
    ```

    The generated plan adds the column directly, which would fail on a non-empty table:

    ```json theme={null}
    {
      "groups": [
        {
          "steps": [
            {
              "sql": "ALTER TABLE users ADD COLUMN email_domain text NOT NULL;",
              "type": "table.column",
              "operation": "create",
              "path": "public.users.email_domain"
            }
          ]
        }
      ]
    }
    ```
  </Step>

  <Step title="Edit Plan">
    Rewrite the step to add the column as nullable first, insert a backfill step, then enforce the constraint. Only the `sql` field is required for hand-added steps:

    ```json theme={null}
    {
      "groups": [
        {
          "steps": [
            {
              "sql": "ALTER TABLE users ADD COLUMN email_domain text;",
              "type": "table.column",
              "operation": "create",
              "path": "public.users.email_domain"
            },
            {
              "sql": "UPDATE users SET email_domain = split_part(email, '@', 2);"
            },
            {
              "sql": "ALTER TABLE users ALTER COLUMN email_domain SET NOT NULL;"
            }
          ]
        }
      ]
    }
    ```

    Steps within a group execute in a single transaction, so the backfill and constraint either all succeed or roll back together.
  </Step>

  <Step title="Apply Plan">
    ```bash theme={null}
    pgschema apply --host localhost --db myapp --user postgres --plan plan.json
    ```

    After apply, the database matches `schema.sql`, so subsequent plans are empty — the data migration naturally runs only once.
  </Step>
</Steps>

<Tip>
  For large tables, run the backfill in batches outside the transaction to avoid long locks. Put `ADD COLUMN` in one plan, batch the `UPDATE` with your own tooling, then run a second plan for `SET NOT NULL`. See [Online DDL](/workflow/online-ddl) for lock-aware patterns.
</Tip>

The same technique applies to any change the diff engine cannot infer, such as rewriting a rename from `DROP` + `ADD` into `ALTER TABLE ... RENAME`, or resolving a `DELETE` on a [config table](/workflow/config-data) that is blocked by a foreign key from user data.