Change a PostgreSQL data type with a shadow column

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

Changing a column type combines several risks that are easier to manage separately: parsing old values, representing future values, moving bytes, rebuilding dependencies, and changing the application contract. An ALTER COLUMN TYPE ... USING statement expresses a conversion, but it does not automatically make that conversion cheap or compatible with running clients.

Suppose invoice amounts were mistakenly stored as text. The goal is a numeric(12,2) amount. A shadow column lets the system establish a typed representation while the original column remains available. It does not remove conversion work. It trades one concentrated change for a period of extra storage, extra writes, and stricter coordination.

Decide what conversion means

Use PostgreSQL 18 and a disposable database. The examples assume the table owner can create a trigger. Before touching real data, decide whether strings such as 1,000.00, an empty string, scientific notation, or amounts with three decimal places are valid business inputs. PostgreSQL's ability to cast a string is not the same as the business accepting that representation.

CREATE TABLE invoice_amount (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount_text text NOT NULL
);
INSERT INTO invoice_amount(amount_text)
VALUES ('19.95'), ('0.00'), ('-4.25');

SELECT id, amount_text
FROM invoice_amount
WHERE NOT pg_input_is_valid(amount_text, 'numeric(12,2)');

The validation function is useful for conversion diagnostics with types that support soft error reporting; numeric is the target here. It does not prove that rounding is acceptable. A value that fits after scale coercion can still lose precision. If amounts must already have exactly two decimal places, impose a separate lexical or numeric invariant agreed with the application team.

Check overflow and sign rules separately. A refund may legitimately be negative. A non-negative-only constraint copied from a different financial table would be a semantic regression, even if every migration command succeeded.

Install the conversion boundary

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE invoice_amount ADD COLUMN amount_value numeric(12,2);
CREATE FUNCTION convert_invoice_amount() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.amount_value := NEW.amount_text::numeric(12,2);
  RETURN NEW;
END;
$$;
CREATE TRIGGER invoice_amount_bridge
BEFORE INSERT OR UPDATE ON invoice_amount
FOR EACH ROW EXECUTE FUNCTION convert_invoice_amount();
COMMIT;

The text column remains authoritative. New malformed writes now fail at the database boundary, even if the old application previously accepted them. That can be desirable, but it is an application behavior change and must be released deliberately. If the old system permits invalid amounts that require human remediation, do not deploy this trigger before defining that remediation workflow.

A BEFORE UPDATE trigger that runs on every update also touches legacy invalid data when an unrelated field changes. Narrowing it to the source column avoids that effect but leaves another path to unsynchronized shadow values. Choose the trigger scope from the invariant you intend to enforce, not from a generic migration recipe.

UPDATE invoice_amount
SET amount_value = amount_text::numeric(12,2)
WHERE id > 0 AND id <= 1000
  AND amount_value IS NULL;

SELECT id, amount_text, amount_value
FROM invoice_amount
WHERE amount_value IS DISTINCT FROM amount_text::numeric(12,2);

Run the update over bounded key ranges with a commit between batches. Persist progress outside a single long-lived transaction. An idempotent batch is useful, but IS NULL is only a progress marker because the source is non-null and a successful conversion cannot produce null here. If null source values were valid, a separate progress marker would be necessary.

Reference conversion pipeline. Parsing validity, business validity, and representation equality are separate checks.

Prove the new invariant before switching

Once the backfill is complete, add and validate a check that compares the typed value to the chosen conversion. A constraint is stronger than a one-time count because it remains active for subsequent writes.

ALTER TABLE invoice_amount ADD CONSTRAINT amount_matches
CHECK (amount_value IS NOT DISTINCT FROM amount_text::numeric(12,2))
NOT VALID;
ALTER TABLE invoice_amount VALIDATE CONSTRAINT amount_matches;

Build new indexes with the intended operators and query patterns. Text ordering and numeric ordering differ: a text sort can place 100 before 20, while numeric ordering cannot. Test exports, report totals, parameter types, JSON serialization, and ORM decimal handling. JavaScript floating-point conversion can reintroduce representation errors outside the database even when PostgreSQL stores exact numeric values.

The cutover needs a coordinated write policy, just like a rename. If both columns become independently writable, define how disagreements are rejected. After the new typed column becomes authoritative, a rollback to the old application is safe only if the old representation continues to be maintained and remains acceptable to that application.

Account for the physical cost

A shadow migration updates rows and can create dead tuples, WAL, replica lag, and index maintenance. It may temporarily require substantially more disk space than either final representation. Measure batch duration and replication recovery in a representative staging environment. Do not call it an online migration merely because each individual batch is short.

A direct type change can be the better choice for a small table or a binary-compatible conversion with an acceptable maintenance window. Review the specific source and target types in the PostgreSQL documentation. A rewrite is not inevitable for every type change, and a shadow column is not inherently safer if the team cannot operate the prolonged compatibility period.

The acceptance test is not simply that all rows cast. It is that old and new clients agree on meaning, writes during backfill remain synchronized, and the reverse path is explicitly bounded by the first value the old system cannot represent.

Sources and review scope

• ALTER TABLE type conversion
• Input validation functions
• Numeric types