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>');
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}
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)
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
"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
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
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)
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)
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)
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)
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 ASso 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_jsonoveraz://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 nativeSeq Scanplan proves the rows really landed in Postgres.INSERT … SELECTappends 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.
