Moving Oracle to PostgreSQL with a Single `INSERT` — No Oracle Client, No Python

How to turn an embedded OLAP engine into a compact, lightning-fast ETL/ELT pipeline with no CSVs and no intermediate storage — and, if needed, parallelize Oracle reads across sessions under one consistent snapshot.

When people talk about DuckDB, they usually think of analytics: local queries to Parquet files, fast aggregations, notebooks, and data science. But DuckDB has another highly practical capability: it can connect data sources and sinks directly within a single SQL plan.

By adding extensions for Oracle and PostgreSQL, complex data migration turns into one elegant query:

INSERT INTO pg.public.orders
SELECT *
FROM oracle_query('ora', 'SELECT * FROM app.orders');

This isn't just shorthand for some external script. DuckDB actually executes the entire pipeline: it reads rows directly from Oracle, transforms them into its highly optimized vector chunks, and streams them directly into PostgreSQL. No intermediate DuckDB table, no CSV files, and no clunky Python loops are needed.

In this architecture, DuckDB doesn't act as the final storage, but as a powerful, in-process ETL/ELT engine.

What We Are Building (The Architecture)

The setup involves three key components:

Oracle ──TNS/TTC──> oracle_scanner ──> DuckDB SQL pipeline ──> postgres extension ──> PostgreSQL

• oracle_scanner reads from Oracle directly via the native TNS/TTC protocol. The huge advantage here: It requires absolutely no Oracle Instant Client, OCI, JDBC, ODBC, Python, or any separate proxy processes. Installing it is one SQL statement from the DuckDB Community Extensions — there is nothing else to put on the machine.
• DuckDB sits in the middle, executing projections, filtering, type casting, and other transformations at vector speed.
• The official postgres extension connects PostgreSQL as a natively accessible catalog for reading and writing.

DuckDB is the "engine in the middle." It understands the schema of both sides and constructs a single, highly optimized physical plan for the INSERT ... SELECT operation.

Connecting Both Databases

oracle_scanner is published in the DuckDB Community Extensions, so installing it is two statements — no build, no -unsigned flag. Version 0.1.0 targets DuckDB v1.5.5, which is the shell version used throughout this article.

INSTALL oracle_scanner FROM community;
LOAD oracle_scanner;

That is the whole installation: the signed binary is downloaded for your platform and it links nothing from an Oracle client. The PostgreSQL extension can be installed from the official DuckDB repository:

INSTALL postgres;
LOAD postgres;

It is highly recommended to store connection credentials securely in the DuckDB Secrets Manager, rather than hardcoding passwords in connection strings:

CREATE SECRET ora (
    TYPE oracle,
    HOST 'oracle.internal',
    PORT 1521,
    SERVICE_NAME 'ORCLPDB1',
    USER 'app_reader',
    PASSWORD '...'
);

CREATE SECRET pg_target (
    TYPE postgres,
    HOST 'postgres.internal',
    PORT 5432,
    DATABASE 'warehouse',
    USER 'etl_writer',
    PASSWORD '...'
);

ATTACH '' AS pg (
    TYPE postgres,
    SECRET pg_target,
    SCHEMA 'public'
);

The target table has to exist before the INSERT — the extension will not invent it for you. You can create it without leaving the DuckDB shell, because the attached catalog is writable:

CREATE TABLE pg.public.orders (
    order_id       BIGINT,
    customer_id    BIGINT,
    created_at     TIMESTAMP,
    amount         DECIMAL(18, 2),
    source_system  VARCHAR
);

(Note: this DDL runs inside PostgreSQL — pg is a remote catalog, not a local DuckDB database, and VARCHAR lands there as text.)

A Single INSERT Instead of a Cumbersome ETL App

Now you can simultaneously extract data, conform it to the target model, and load it:

INSERT INTO pg.public.orders (
    order_id,
    customer_id,
    created_at,
    amount,
    source_system
)
SELECT
    ORDER_ID::BIGINT,
    CUSTOMER_ID::BIGINT,
    CAST(CREATED_AT AS TIMESTAMP),
    CAST(AMOUNT AS DECIMAL(18, 2)),
    'oracle' AS source_system
FROM oracle_query(
    'ora',
    'SELECT order_id, customer_id, created_at, amount
       FROM app.orders
      WHERE created_at >= :watermark',
    {'watermark': TIMESTAMP '2026-08-01 00:00:00'}
);

Key benefits of this approach:

• Clear Extract vs. Transform boundaries: The watermark filter runs inside Oracle — it is part of the statement you send, so only matching rows ever cross the network. DuckDB then handles type casting and adds the new source_system column. (This is deliberate hand-written pushdown, not the automatic filter pushdown the extension can do for ATTACHed Oracle tables; that one is opt-in via SET oracle_filter_pushdown = true.)
• Zero-friction loading: The output of the SELECT instantly becomes the input for writing to PostgreSQL.
• Values stay values: The bind parameter :watermark is sent as a typed bind, never concatenated into the SQL text — so a value can never turn into syntax. The statement text is still yours to keep static.

Whether you call this ETL or ELT depends on where you draw the line. But practically, the key advantage is that your entire data movement logic is defined declaratively in a single SQL query, eliminating the need to write, deploy, and maintain a separate data pumping service.

Why Not Just Use oracle_fdw in PostgreSQL?

A natural question arises: if the goal is to query Oracle from PostgreSQL, why not just use the standard Foreign Data Wrapper (oracle_fdw) directly inside Postgres?

Here is why the DuckDB approach is fundamentally different and often superior:

1. Zero Client Dependencies (No OCI Nightmare): oracle_fdw requires installing Oracle Instant Client and OCI libraries directly on the PostgreSQL server OS. This is often an administrative nightmare and sometimes strictly prohibited in managed database environments (like AWS RDS or GCP Cloud SQL). In contrast, DuckDB's oracle_scanner speaks the native TNS/TTC wire protocol. It requires zero Oracle binaries.
2. Decoupled Workload: With FDW, the heavy lifting of data extraction and type conversion happens inside your primary PostgreSQL database, potentially impacting production performance. By placing DuckDB in the middle (e.g., in a separate container, CI/CD runner, or sidecar), you offload the entire ETL workload.
3. Vectorized Transformations: DuckDB processes data in columnar vectors, so type conversion and expression evaluation happen a batch at a time before the result is written to PostgreSQL. oracle_fdw prefetches from Oracle in batches too, but hands rows to the PostgreSQL executor one tuple at a time, and every transformation you add runs in that row-at-a-time engine.
4. Native Range-Sharded Parallelism: As we'll see next, DuckDB makes it trivial to split reads into perfectly consistent parallel shards, which is incredibly difficult to achieve purely with FDW.

Why the Table Doesn't Need to Fit in Memory

oracle_query is implemented as a streaming table function. It requests the next batch of rows from Oracle (sized to match a standard DuckDB vector), hands the chunk to the next operator, and only then reads further.

As a result, a simple INSERT ... SELECT pipeline does not materialize the entire source table in RAM. Memory is only consumed by the active chunks and operator buffers, making this approach extremely resource-efficient.

Caveat: Streaming doesn't defy the laws of query physics. Global sorts, massive GROUP BYs, window functions, or unoptimized joins might still require significant memory or spill to disk. If your goal is pure data transfer, avoid adding blocking operators unless necessary.

Built-in Backpressure: If PostgreSQL ingest slows down, the pipeline won't frantically read ahead from Oracle and overwhelm your system. The consumer dictates the pace, ensuring a stable, controlled, and resilient data flow.

Scaling Up: When One Oracle Session Isn't Enough

A standard oracle_query uses a single Oracle session and one read thread. While perfect for incremental loads, a massive full-load might hit network round-trip or single-session throughput bottlenecks.

To drastically accelerate throughput, the source can be partitioned by numeric key ranges:

INSERT INTO pg.public.orders (
    order_id,
    customer_id,
    created_at,
    amount,
    source_system
)
SELECT
    ORDER_ID::BIGINT,
    CUSTOMER_ID::BIGINT,
    CAST(CREATED_AT AS TIMESTAMP),
    CAST(AMOUNT AS DECIMAL(18, 2)),
    'oracle'
FROM oracle_scan_parallel(
    'ora',
    'APP.ORDERS',
    'ORDER_ID',
    shards := 8
);

How oracle_scan_parallel works its magic:

1. Grabs the current Oracle System Change Number (SCN).
2. Describes the table and checks the key is NUMBER, then reads the min and max key values — already AS OF SCN.
3. Divides the range into shards (half-open, the last one closing on the maximum, so every key belongs to exactly one shard).
4. Opens multiple parallel sessions.
5. Crucially: Reads every range AS OF SCN using that same, single SCN.
6. Adds one extra shard for rows whose key is NULL — but only if the table actually has any, so you never pay for an idle session.

The unified SCN is the critical component here. If you merely opened eight random connections, each would see a slightly different point in time. A row could update mid-flight and appear twice or vanish entirely. Using a Flashback snapshot guarantees that the parallel result is a perfectly consistent snapshot of a single logical table version.

Requirements: The key must be an Oracle NUMBER with integer bounds — a non-numeric key is refused by name rather than approximated. You also need EXECUTE privileges on SYS.DBMS_FLASHBACK and FLASHBACK on the table. The shard count defaults to DuckDB's thread count and is capped at 256; it is also clamped to the width of the key range, so a table with five distinct keys gets five shards no matter what you ask for. The table name may be schema-qualified ('APP.ORDERS').

Note: This is range sharding, not automatic statistical balancing. A highly skewed key might cause some shards to finish early. Overall speed is still gated by your slowest link (source, transformations, network, or target write), so benchmark and adjust shards carefully to avoid overwhelming either database.

Cross-Database Reconciliation — and What It Costs

A migration is not finished until you have proven the two sides agree, and this is where querying both engines from one session pays off. Reconciliation becomes a SELECT instead of a Python script that pages both databases into memory.

But the naive version of that SELECT is a full extract in disguise, and it is worth seeing why before you point it at a 200-million-row table:

-- Looks declarative. Reads both tables end to end.
SELECT order_id, amount
FROM oracle_query('ora', 'SELECT order_id, amount FROM app.orders')
EXCEPT
SELECT order_id, amount
FROM pg.public.orders;

The join and the EXCEPT happen in DuckDB, so both sides have to arrive there first. oracle_query sends exactly the text you wrote — no filter is added on the way. Checking v$sql on the Oracle side after running a cross-database join confirms it: what arrives is

SELECT "ORDER_ID", "AMOUNT" FROM "ORDERS"

with no WHERE clause at all. The same is true of an ATTACHed table: a join predicate against a PostgreSQL table is never pushed into Oracle, because Oracle cannot see the other table. And SET oracle_filter_pushdown = true does not change that — it pushes simple constant predicates (AMOUNT > 100, IS NULL, IN (…), an equality on text), and it is off by default. Even SELECT count(*) over an attached table reads one row per row over the wire; DuckDB does the counting.

Compare summaries first, rows only where they disagree

The fix is the same hand-written pushdown as in the load itself: make each database compute its own summary, and compare the summaries. Bucket by something that already exists in the data — a month, a day, a key range:

-- Oracle computes this; one row per bucket crosses the network
CREATE OR REPLACE TABLE ora_buckets AS
SELECT * FROM oracle_query('ora', $$
    SELECT TO_CHAR(created_at, 'YYYY-MM') AS bucket,
           COUNT(*)                       AS rows_cnt,
           SUM(amount)                    AS amount_sum
      FROM app.orders
     GROUP BY TO_CHAR(created_at, 'YYYY-MM')
$$);

CREATE OR REPLACE TABLE pg_buckets AS
SELECT to_char(created_at, 'YYYY-MM') AS bucket,
       count(*)                       AS rows_cnt,
       sum(amount)                    AS amount_sum
  FROM pg.public.orders
 GROUP BY 1;

SELECT * FROM ora_buckets EXCEPT SELECT * FROM pg_buckets;

A hundred buckets is a hundred rows over the wire, whatever the table weighs. Now descend, and only into what actually disagreed — with the bucket as a bind parameter, so the filter runs inside Oracle:

SELECT order_id, amount
FROM oracle_query('ora', $$
    SELECT order_id, amount
      FROM app.orders
     WHERE created_at >= TO_DATE(:bucket, 'YYYY-MM')
       AND created_at <  ADD_MONTHS(TO_DATE(:bucket, 'YYYY-MM'), 1)
$$, {'bucket': '2026-07'})
EXCEPT
SELECT order_id, amount
FROM pg.public.orders
WHERE created_at >= DATE '2026-07-01' AND created_at < DATE '2026-08-01';

Counts and sums catch missing and duplicated rows, but not a changed value that keeps the total intact. When you need content equality, hash the row — and note that this only works if both sides build the same text: LOWER(STANDARD_HASH(x, 'MD5')) in Oracle and md5(x) in PostgreSQL are the same digest, which is easy to verify (10|ACCOUNTING hashes to cb357227… on both). Getting there means pinning the representation yourself: format numbers and dates explicitly, and give NULL a marker, because 'a' || NULL is NULL in Oracle and one side will silently disagree with itself.

If you are going to scan Oracle more than once, scan it once

Every oracle_query is a fresh statement against Oracle, so three reconciliation queries over the same table are three full reads. Land it once and work locally — and for a large table, read it through several sessions at a single SCN:

CREATE TABLE ora_orders AS
SELECT * FROM oracle_scan_parallel('ora', 'APP.ORDERS', 'ORDER_ID', shards := 4);

DuckDB spills to disk, so this is bounded by disk rather than RAM — the same property that lets the load itself run on a laptop.

Is This a Replacement for Airflow and CDC?

No — and recognizing this makes the pattern much easier to apply correctly.

DuckDB beautifully handles the data plane of a single load: connect, read, transform, and write. However, a standalone SQL query does not handle:

• Scheduling and orchestration
• Watermark state management
• Retries and alerting
• Deduplication on rerun
• Schema evolution
• Continuous redo-log CDC
• Row count and checksum reconciliations

For one-off migrations, periodic batches, and backfills, this approach is phenomenal. For a production pipeline, wrap this query in a standard orchestrator and explicitly define your rerun semantics. (Pro tip: For large reloads, write to a staging PostgreSQL table first, validate, and then swap or merge).

Pre-Flight Checklist

• Types: NUMBER(p,0) up to 18 digits becomes BIGINT, NUMBER(p,s) becomes DECIMAL, and unconstrained NUMBER is returned as a string to preserve exact precision. Oracle DATE maps to TIMESTAMP. Unsupported columns are safely rejected during the bind phase.
• LOBs: Reading CLOB, NCLOB and BLOB works, but the row carries only a locator, so every value costs extra round-trips: 2,000 rows with a 2,000-character CLOB measured 1.22 s, against 0.004 s for the same rows without that column. Don't SELECT LOBs you don't need — and note that writing a LOB back to Oracle is not supported, which matters only if you ever reverse the direction.
• Consistency: Parallel scan yields a consistent Oracle snapshot, but this is not a distributed transaction. Design your cleanup and retry strategies on the PostgreSQL side.
• Row Order: Shards execute concurrently, so row insertion order is undefined (which is standard for relational tables).
• Security First: For Oracle Autonomous Database, the extension natively reads wallet ZIPs in-memory and connects via TCPS with mandatory certificate validation (TLS verification cannot be disabled).
• Validation: Always verify row counts and key-range aggregates post-migration — computed inside each database and compared as summaries, not by pulling both tables into DuckDB and joining them.

Conclusion: A Simpler Pipeline

DuckDB is typically viewed as a lightweight analytical tool. But its embedded architecture combined with extensible data sources turns it into a versatile SQL data mover.

Instead of writing a bloated application that:

1. Loads an Oracle driver
2. Manually loops and converts types
3. Manages queues or writes fragile temporary files
4. Loads a PostgreSQL driver
5. Batches inserts manually
6. Fights memory limits and backpressure

You get a purely declarative pipeline:

INSERT INTO postgres_target
SELECT transformed_columns
FROM oracle_source;

And when you need more power, you scale out with consistent parallel range-shards without breaking a sweat.

This isn't a bloated enterprise integration bus or complex CDC. It's something much sharper: a compact, lightning-fast batch ETL/ELT pipeline with zero intermediate layers, running exactly where DuckDB runs.

Sometimes, the ultimate data pipeline really is just a single INSERT.

Links

• oracle_scanner: Source Code & Documentation
• PostgreSQL extension — DuckDB documentation
• Writing Data to PostgreSQL from DuckDB
• DuckDB INSERT statement

Every SQL statement in this article was run as written — Oracle Database 19c and PostgreSQL 16 in containers, DuckDB v1.5.5 with oracle_scanner 0.1.0 installed from the Community Extensions and the official postgres extension.