PostgreSQL is the natural home for FoxPro data when you'd rather not pay for SQL Server licensing — it's open source, ACID-compliant, richly typed, and free. But "load the DBF into Postgres" hides a surprising amount of detail. This guide walks through what actually matters: type mapping, encoding, deleted records, memo files, and proving the result is correct.
It's written to be useful whether you're doing this yourself or evaluating someone to do it for you. If you'd rather hand the whole thing off, that's what our DBF migration service is for — but you should understand the moving parts either way.
Why PostgreSQL for FoxPro data?
FoxPro's DBF tables are file-based: no real concurrency control, hard size ceilings, and integrity enforced in application code rather than the database. PostgreSQL fixes all of that, and for a FoxPro shop it brings specific advantages:
- No licensing cost. Unlike SQL Server's per-core licensing, PostgreSQL is free — the budget goes to engineering, not licenses.
- Rich, exact types. numeric for money without floating-point error, native date/timestamp, boolean, and jsonb when you need it.
- Real integrity. Foreign keys, constraints, and transactions enforce the rules your VFP code used to enforce by hand.
- Portability. Runs anywhere — any cloud or your own servers — with no single-vendor gravity.
DBF → PostgreSQL type mapping
Getting types right is the foundation. Here's the mapping that preserves meaning rather than just moving bytes:
| FoxPro / DBF type | PostgreSQL type & notes |
|---|---|
| Character (C) | varchar(n) or text — trim trailing spaces; transcode from the source code page. |
| Numeric (N) | numeric(p,s) — keep exact precision and scale. |
| Currency (Y) | numeric(19,4) — preserves the fixed four-decimal precision; never use float for money. |
| Integer (I) | integer; VFP9 autoinc → identity/serial. |
| Double (B) / Float (F) | double precision — or numeric if exactness matters. |
| Date (D) | date. |
| DateTime (T) | timestamp. |
| Logical (L) | boolean. |
| Memo (M) | text — read from the companion .fpt file. |
| General (G) / OLE | bytea, or extract embedded objects to files and store a path. |
| Varbinary / Blob (VFP9) | bytea. |
The gotchas that corrupt data quietly
Character encoding & code pages
DBF files carry a code-page byte. Many are Windows-1252 or an OEM code page; PostgreSQL is typically UTF-8. Copy the raw bytes without transcoding and every accented name, é, ñ, and £ comes through mangled. Read the source code page and convert to UTF-8 on the way in — and spot-check real records that contain non-ASCII characters.
Deleted-but-not-packed records
A DBF "delete" only sets a marker byte; the row stays in the file until a PACK. If your tool reads the file directly, those deleted rows are still there. Decide deliberately: skip them (most common), or import them with a deleted flag if you need the audit trail. The wrong default silently resurrects data someone deleted years ago.
Nulls, empty, and field widths
VFP distinguishes null from empty, pads character fields with spaces, and has its own rules for empty dates. Map these intentionally — an empty VFP date is not 1899-12-30 in your new system unless you decide it is, and a space-padded code is not the same as a trimmed one when you start joining on it.
Identifiers & reserved words
PostgreSQL folds unquoted identifiers to lowercase and has reserved words FoxPro doesn't. Field or table names like USER, ORDER, or mixed-case names need quoting or renaming — decide on a naming convention up front so application code isn't chasing it later.
The pattern across all of these: a byte-for-byte copy is easy and wrong. The value is in moving meaning — and most of the meaning lives in the edge cases.
A reliable migration process
- 1. Discover. Inventory every DBF/DBC, its structure, indexes, memo files, and code page — and the relationships the VFP code enforces.
- 2. Design the schema. Build a normalized PostgreSQL schema with correct types, foreign keys, and constraints — not one flat table per DBF.
- 3. Extract & transform. Read records (handling deletes and encoding), map types, and stage the data.
- 4. Load. Bulk-load with COPY for speed, then add constraints and indexes.
- 5. Validate. Compare row counts, checksums, and sampled records against the source until they reconcile exactly. This is the step that turns "migrated" into "trustworthy."
- 6. Sync & cut over. Keep PostgreSQL current with incremental sync while FoxPro runs, then cut over on your schedule.
Do it yourself, or hand it off?
For a handful of clean, simple tables, a careful engineer with a good DBF library can do this well. For a real production database — dozens of interrelated tables, memo and general fields, code-page quirks, and relationships hidden in 20 years of code — the discovery and validation work dominates, and that's exactly what a professional migration is built around. (We dig into that trade-off in automated tools vs. professional migration.)
Bottom line: PostgreSQL is an excellent, license-free home for FoxPro data. Just respect the details — types, encoding, deletes, and validation — because that's where a migration is won or quietly lost.