SQLite’s storage classes and PostgreSQL’s declared types need deliberate translation. This fixture stores done as integer0/1, cents as integers and timestamps as text. The target uses BOOLEAN, BIGINT and TIMESTAMPTZ with explicit constraints.
Normalize before inserting. Reject a flag such as2, a negative amount or unsupported timestamp precision instead of coercing it silently. Currency remains integer cents, so0.29 does not pass through floating-point arithmetic. The fixture’s title and UTC timestamp policy is visible in code and tests.
The target identity is GENERATED ALWAYS AS IDENTITY. Controlled imports preserve known IDs with OVERRIDING SYSTEM VALUE, then synchronize the sequence to the imported maximum. An ordinary later insert should generate43 after the fixture’s maximum42.
Sequences are not gapless and their allocations are not rolled back like ordinary rows. A failed insert or rolled-back transaction can consume a value. Do not use consecutive IDs as proof of transaction success or assume the next ID always equals the current row count plus one.
package main
import ("fmt";"time")
func main(){
stamp,_:=time.Parse(time.RFC3339,"2024-01-01T01:00:00+01:00");fmt.Println(stamp.UTC().Format(time.RFC3339));fmt.Println(int64(29))
}TIMESTAMPTZ represents an instant; it does not preserve the original textual offset. The lab converts accepted whole-second RFC3339 values to UTC. It rejects nonzero fractional seconds because a general migration needs a declared precision policy rather than hidden truncation.
Constraints remain part of the target schema. Foreign keys, nonnull fields, positive identities and nonnegative bounded cents are tested against real PostgreSQL. A Go struct alone cannot replace database constraints for every writer.
package main
import ("fmt")
func main(){
for _,flag:=range []int{0,1,2}{fmt.Println(flag,flag==0||flag==1)}
}Implement canonical value conversion and reject unsupported inputs. Explain integer cents, boolean flags, UTC instants and identity-sequence handling before applying migrations.
The records survive the database boundary.
This snapshot came from the owned local lab. Choose an imported identity to inspect its actual source and canonical target. Reconciliation covers all fields, before a separate successful application write brings the final note count to four.
- 1 · Schemaowners · notes · events
- 2 · Indexowner lookup
- Reapplyzero pending versions
- Bad version 3DDL/history rolled back
SQLite source · ID 1
{
"id": 1,
"owner_id": 1,
"title": " first note ",
"done": 0,
"cents": 29,
"created_at": "2024-01-01T01:00:00+01:00"
}PostgreSQL canonical row
{
"id": 1,
"owner_id": 1,
"title": "first note",
"done": false,
"cents": 29,
"created_at": "2024-01-01T00:00:00Z"
}done → booleancents → bigintcreated_at → timestamp with time zone3 source rows · 3 target rows · no missing, unexpected or changed identities
Inspect the separate database checks
- Quoted owner name
- 2 matching rows with a bound parameter
- Injection-shaped name
- 0 matches · value remains data
- Next generated identity
- 43 after imported maximum 42
- Failed note/event transaction
- Partial note write rolled back
- Foreign key and cents constraints
- Actual invalid inserts rejected
- Canceled query
- Cancellation observed; pool usable afterward
- Application role
- Nonsuperuser · pool maximum two connections
- Owned cleanup
- Cluster stop confirmed; fixture directory removed