DEV Community

Cover image for Load CSV and JSON from Azure Blob into Postgres with pg_duckdb
Franck Pachot
Franck Pachot

Posted on

Load CSV and JSON from Azure Blob into Postgres with pg_duckdb

#postgres #duckdb #azure #etl

Data arrives as files. A vendor drops a CSV in a container, an upstream system exports newline-delimited JSON, a daily extract lands in Blob. The usual way into Postgres is a client-side load tool and a hand-written COPY with a column list. With pg_duckdb you can skip most of that: point read_csv() / read_json() at the file in Blob, let DuckDB infer the schema, and CREATE TABLE AS straight into a normal PostgreSQL table — or just query the file in place and never import it.

Setup

Only the Blob secret is needed:

CREATE EXTENSION IF NOT EXISTS pg_duckdb;
SELECT duckdb.create_azure_secret('<blob connection string>');
Enter fullscreen mode Exit fullscreen mode

The files in Blob

A vendor list as CSV and an event stream as newline-delimited JSON:

az://lake/incoming/vendors.csv
   vendor_id,name,country,active,rating
   1,Acme Corp,US,true,4.5
   2,Globex,CH,false,3.9
   3,Initech,FR,true,4.1
   4,Umbrella,DE,true,2.8
   5,Soylent,JP,false,4.9

az://lake/incoming/events.json
   {"event_id":101,"ts":"2026-01-05T10:00:00","user":"alice","action":"login","value":1}
   {"event_id":102,"ts":"2026-01-05T10:02:13","user":"bob","action":"purchase","value":42}
   {"event_id":103,"ts":"2026-01-05T10:05:41","user":"alice","action":"logout","value":1}
Enter fullscreen mode Exit fullscreen mode

The image cover of this blog post shows those files in Microsoft Azure Storage Explorer. I can read them from Azure Database for PostgreSQL with the pg_duckdb extension enabled.

Read a CSV with inferred types

read_csv() parses and infers types from the file — no column list, no cast boilerplate to look at the data:

postgres=> SELECT r['vendor_id'], r['name'], r['country'], r['active'], r['rating']
           FROM read_csv('az://lake/incoming/vendors.csv') r
;
 vendor_id |   name    | country | active | rating
-----------+-----------+---------+--------+--------
 1         | Acme Corp | US      | t      | 4.5
 2         | Globex    | CH      | f      | 3.9
 3         | Initech   | FR      | t      | 4.1
 4         | Umbrella  | DE      | t      | 2.8
 5         | Soylent   | JP      | f      | 4.9
(5 rows)
Enter fullscreen mode Exit fullscreen mode

Note active came back as a boolean and rating as a number — DuckDB inferred them from the file; you didn't declare anything.

Materialize into a real PostgreSQL table

To bring it in, wrap it in CREATE TABLE AS and cast each column to the Postgres type you want. (Casting on the way in is good practice — it pins the schema rather than trusting inference, and it gives money/number columns an explicit precision.)

postgres=> CREATE TABLE vendors AS
           SELECT r['vendor_id']::int       AS vendor_id,
                  r['name']::text           AS name,
                  r['country']::text        AS country,
                  r['active']::boolean      AS active,
                  r['rating']::numeric(3,1) AS rating
           FROM read_csv('az://lake/incoming/vendors.csv') r
;
SELECT 5
Enter fullscreen mode Exit fullscreen mode

"vendors" is now an ordinary PostgreSQL table — index it, join it, update it, EXPLAIN it like any other. Two plans make the point.

With duckdb.force_execution = true, DuckDB will still scan the table (because it can), through a TABLE_SCAN on Table: vendors — which already proves "vendors" is a real Postgres relation, not a file handle:

postgres=> SET duckdb.force_execution = true;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM vendors WHERE active
;
                   QUERY PLAN
-------------------------------------------------
 Custom Scan (DuckDBScan) (actual time=0.001..0.021 rows=0.00 loops=1)
   DuckDB Execution Plan:

 ┌─────────────────────────────────────┐
 │┌───────────────────────────────────┐│
 ││    Query Profiling Information    ││
 │└───────────────────────────────────┘│
 └─────────────────────────────────────┘
 ┌────────────────────────────────────────────────┐
 │┌──────────────────────────────────────────────┐│
 ││             Total Time: 0.0012s              ││
 │└──────────────────────────────────────────────┘│
 └────────────────────────────────────────────────┘
 ┌───────────────────────────┐
 │           QUERY           │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │      EXPLAIN_ANALYZE      │
 │    ────────────────────   │
 │           0 rows          │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │    UNGROUPED_AGGREGATE    │
 │    ────────────────────   │
 │        Aggregates:        │
 │        count_star()       │
 │                           │
 │           1 row           │
 │          (0.00s)          │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         TABLE_SCAN        │
 │    ────────────────────   │
 │       Table: vendors      │
 │      Filters: active      │
 │                           │
 │           3 rows          │
 │          (0.00s)          │
 └───────────────────────────┘
 Planning:
   Buffers: shared hit=1
 Planning Time: 1.196 ms
 Execution Time: 1.034 ms
Enter fullscreen mode Exit fullscreen mode

The TABLE_SCAN with Table: vendors and 3 rows (the active vendors) proves "vendors" is a real Postgres relation DuckDB reads, not a file handle. Turn forcing back off (the default) and the data is served entirely by the PostgreSQL executor — no DuckDB in the picture at all:

postgres=> SET duckdb.force_execution = false;
SET
postgres=> EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM vendors WHERE active
;
                           QUERY PLAN
-----------------------------------------------------------------
 Aggregate (actual time=0.024..0.025 rows=1.00 loops=1)
   Buffers: shared hit=1
   ->  Seq Scan on vendors (actual time=0.018..0.019 rows=3.00 loops=1)
         Filter: active
         Rows Removed by Filter: 2
         Buffers: shared hit=1
 Planning Time: 0.071 ms
 Execution Time: 0.062 ms
Enter fullscreen mode Exit fullscreen mode

That Seq Scan on vendors is the proof the load worked: the rows are physically in Postgres, queryable by the native engine like any table you created by hand.

Same thing for JSON

read_json() handles newline-delimited JSON (and arrays), inferring fields:

postgres=> SELECT r['event_id'], r['user'], r['action'], r['value']
           FROM read_json('az://lake/incoming/events.json') r
;
 event_id | user  |  action  | value
----------+-------+----------+-------
 101      | alice | login    | 1
 102      | bob   | purchase | 42
 103      | alice | logout   | 1
(3 rows)
Enter fullscreen mode Exit fullscreen mode

And into a table, parsing the timestamp as you go:

postgres=> CREATE TABLE events_log AS
           SELECT r['event_id']::int   AS event_id,
                  (r['ts'])::timestamp AS ts,
                  r['user']::text      AS usr,
                  r['action']::text    AS action,
                  r['value']::int      AS value
           FROM read_json('az://lake/incoming/events.json') r
;
SELECT 3

postgres=> SELECT * FROM events_log ORDER BY event_id
;
 event_id |         ts          |  usr  |  action  | value
----------+---------------------+-------+----------+-------
 101      | 2026-01-05 10:00:00 | alice | login    | 1
 102      | 2026-01-05 10:02:13 | bob   | purchase | 42
 103      | 2026-01-05 10:05:41 | alice | logout   | 1
(3 rows)
Enter fullscreen mode Exit fullscreen mode

For a recurring load, use INSERT INTO events_log SELECT … FROM read_json(…) instead of CREATE TABLE AS, and point the path at the new file (or a glob over a folder of drops — see the partitioned-lakes post).

Or don't import at all

Sometimes you don't need the data in Postgres — you just need an answer. You can aggregate the Blob file directly:

postgres=> SELECT r['country'] AS country,
                  count(*)     AS n,
                  avg((r['rating'])::numeric(3,1)) AS avg_rating
           FROM read_csv('az://lake/incoming/vendors.csv') r
           GROUP BY r['country']
           ORDER BY 1
;
 country | n | avg_rating
---------+---+------------
 CH      | 1 | 3.9
 DE      | 1 | 2.8
 FR      | 1 | 4.1
 JP      | 1 | 4.9
 US      | 1 | 4.5
(5 rows)
Enter fullscreen mode Exit fullscreen mode

That's the nice property of this approach: the same read_csv() / read_json() is either a one-off query or the source of an import, depending on whether you wrap it in CREATE TABLE AS. Query in place for exploration and ad-hoc checks. Materialize when you need indexes, constraints, or OLTP access.

The reverse direction: writing a result back out

Loading isn't one-way. You can also write a Postgres query result back to Blob as Parquet — handy for publishing an aggregate or an extract. On Azure Database for PostgreSQL the plain COPY … TO 'az://…' syntax is blocked for a normal login (it needs superuser), so you drive the write through the DuckDB engine with duckdb.raw_query, which authorizes it with the secret and the az:// allow-list — the object-storage controls PostgreSQL's server-file gate has no say over:

postgres=> SELECT duckdb.raw_query($$
             COPY (SELECT country, count(*) AS n
                   FROM pgduckdb.public.vendors GROUP BY country)
             TO 'az://lake/exports/vendors_by_country.parquet' (FORMAT parquet)
           $$)
;
 raw_query
-----------

(1 row)
Enter fullscreen mode Exit fullscreen mode

Note the pgduckdb.public. prefix on the table: inside raw_query you're in DuckDB's catalog, not PostgreSQL's search path, so a bare vendors won't resolve. The archive post covers this write path in full.

A few practical notes

  • Cast on import. Inference is convenient for a look, but pin the types in your CREATE TABLE AS so the table schema is deterministic — and give decimals an explicit precision (numeric(p,s)), which also keeps later DuckDB queries on the table from falling back (see the numeric gotcha side-note).
  • CSV options mirror DuckDB's, passed with PostgreSQL named-arg syntax: read_csv('az://…/f.csv', delim := ';', header := true) and so on.
  • Globs work here too. read_csv('az://lake/incoming/*.csv') loads a whole drop folder in one statement.
  • Big loads stream. DuckDB reads the file in chunks, so you're not limited to what fits in memory for the parse.

Takeaways

  • read_csv / read_json over az:// read files straight from Blob with type inference — no pre-defined table, no external load tool.
  • CREATE TABLE AS SELECT … FROM read_csv(…) materializes into a normal Postgres table (cast the columns to pin the schema); the native Seq Scan plan proves the rows really landed in Postgres. INSERT … SELECT appends for recurring loads.
  • Or skip the import and aggregate the file in place — same function, your choice.
  • Globs and DuckDB's CSV options (delim :=, header :=, …) are available via PostgreSQL named-argument syntax.

The archive post wrote cold data out to Blob by hand, step by step. In the last post of the series I turn that export-verify-delete cycle into a single durable, scheduled workflow with pg_durable — so a crash mid-way resumes instead of re-exporting.

Top comments (0)