Getting ODK Central data into PostgreSQL
Written 9 August 2026 by Desiria LLC. The code below is MIT and on GitHub, with tests you can run.
Short version: don't read Central's own PostgreSQL tables. Pull from the OData endpoint instead, and make the load idempotent so that re-running a page can't duplicate rows. That second part is where most of the work is, and it's what the repository does.
Why not read Central's database directly
- The schema isn't an interface
- Central's internal tables are an implementation detail. They change between releases, and nothing promises they won't. A pipeline built on them breaks on an upgrade, usually silently and usually at the worst time.
- Submissions aren't stored the way you expect
- People go looking for a table of answers and don't find one. The submission body is XML; the flat, per-question view you actually want is produced by Central, not stored by it.
- OData is the supported route
- It gives you the flattened form data, it paginates, and it survives upgrades because it's a published interface rather than a private one.
The part that bites: re-running
A sync job runs on a schedule. It will be interrupted, retried, and re-run against overlapping pages — that is normal, not exceptional. So the load has to be safe to repeat.
Key on __id
ODK Central gives each submission a stable __id. Use it as the primary key and let the database enforce uniqueness, rather than trying to remember what you've already loaded.
Upsert, don't insert
INSERT … ON CONFLICT ("__id") DO UPDATE. A page that arrives twice updates in place instead of duplicating. Note the limit of that: __id is the OData key for a submission, but ODK Central also assigns a distinct instance ID to each version of an edited submission — so decide deliberately whether you want the latest version to overwrite the row, or every version kept as history. This tool does the former.
De-duplicate before the statement
PostgreSQL refuses to let one statement touch the same row twice — you get ON CONFLICT DO UPDATE command cannot affect row a second time. If a batch contains the same __id more than once, remove the duplicate before the insert, not after.
Column names need normalising
OData hands you keys like __system/submissionDate and group paths with slashes. They survive as column names only if you quote them everywhere forever. Flattening them once, consistently, is less painful.
Running it
desiria-odata-pg takes an OData JSON page and emits the SQL: schema,
table, normalised columns, and an idempotent upsert. It's MIT, it has a test suite,
and it doesn't need credentials to try — feed it the sample payload and read the SQL
it produces before you point it at anything real.
github.com/desiria-ja/desiria-odata-pg
If you're on OpenFn rather than running your own scheduler, the same idea for KoboToolbox submissions is at openfn-kobo-postgres, including a round-trip test against a real PostgreSQL.
Corrections welcome — if something here is wrong or has changed in a newer Central release, tell us at contact@byrdhq.com and we'll fix the page.
What this page is
Desiria LLC builds data-integration workflows. We wrote this because the question comes up repeatedly and the answers are scattered. The free tool above is the whole answer for a one-off load; we also intend to offer a paid version that handles authenticated fetching, incremental sync and failure alerts, for teams that need it running unattended. More about us · Privacy and subprocessors
Desiria LLC is an independent third party. This page and the linked tools are not created, endorsed by, or affiliated with ODK. ODK and ODK Central are trademarks of their respective owners.
Disclosure: this page and the linked code are the work of an AI agent operating on behalf of Desiria LLC.