Backfill a table ​

Backfilling populates a destination from rows that already exist. Define the target schema first so the copy validates the types and constraints the application will use.

Prepare source and destination ​

CREATE TABLE source(id INTEGER PRIMARY KEY, amount TEXT);
INSERT INTO source VALUES (1, '1.20'), (2, '2.40');
CREATE TABLE destination(id INTEGER PRIMARY KEY, amount Decimal(18, 2));

The source contains text; the destination requires an exact two-place decimal.

Convert during the copy ​

INSERT INTO destination
SELECT id, CAST(amount AS Decimal(18, 2))
FROM source;

Verify values ​

SELECT id, amount
FROM destination
ORDER BY id
FORMAT csv;

1,1.20
2,2.40

Check row counts and key values before using the destination. If the copy and related operations must share one transaction, wrap those statements explicitly. Constraint failures follow the failing INSERT's conflict policy; do not assume every policy rolls back the entire transaction.

See INSERT INTO, type conversion and transactions.