---
name: duckdb-pst
description: Query Microsoft Outlook PST archives with the duckdb-pst DuckDB extension. Use when the user asks to inspect, search, filter, count, extract, join, or export data from a `.pst` file, mailbox, or the Enron FERC corpus. Do not use it to create or modify PST files.
---

# Query Outlook PST archives with duckdb-pst

`duckdb-pst` is a DuckDB extension that reads Microsoft PST files directly. Every message, contact, appointment, folder, and attachment is a row you can `SELECT`, `WHERE`, `JOIN`, aggregate, and export like any other DuckDB table.

Use the [duckdb-pst repository](https://github.com/intellekthq/duckdb-pst) for the complete API and schema reference.

## Load the extension

```sql
INSTALL pst FROM community;
LOAD pst;
```

If the community CDN does not carry a build for your DuckDB version, use a locally-built extension binary. The DuckDB CLI needs `-unsigned` in this mode:

```
duckdb -unsigned -c "LOAD '/path/to/pst.duckdb_extension';"
```

## Read messages

```sql
-- Corpus-wide count. No bodies are read for count(*).
SELECT count(*) FROM read_pst_messages('data/*.pst');

-- Message headers only; body is skipped by projection.
SELECT pst_path, node_id, message_delivery_time, sender_name, subject
FROM read_pst_messages('data/*.pst')
WHERE subject ILIKE '%California%'
LIMIT 20;

-- Full bodies. Bodies are capped at 1 MB by default; set to 0 to read all.
SELECT pst_path, node_id, subject, body
FROM read_pst_messages('data/*.pst', read_body_size_bytes := 0)
WHERE body ILIKE '%acquisition%'
LIMIT 5;
```

## Read attachments

Attachments are a nested `LIST<STRUCT>` on each message row. `UNNEST` to iterate.

```sql
-- Metadata only (filenames, mime types, sizes).
SELECT m.pst_path, m.node_id AS message_node_id,
       a.node_id AS attachment_node_id, a.filename, a.mime_type, a.size
FROM read_pst_messages('data/*.pst') m,
     UNNEST(m.attachments) t(a)
WHERE a.filename ILIKE '%.pdf'
LIMIT 10;

-- Pull raw bytes for a known attachment. Replace the path and IDs.
SELECT m.pst_path, m.node_id AS message_node_id,
       a.node_id AS attachment_node_id, a.bytes
FROM read_pst_messages('/path/to/mailbox.pst', read_attachment_body := true) m,
     UNNEST(m.attachments) t(a)
WHERE m.node_id = 12345 AND a.node_id = 67890;
```

## Message-type variants

`read_pst_messages` returns all message types with the base message schema. Type-specific readers filter during planning and return richer fields:

| Function | MAPI class | Notes |
|---|---|---|
| `read_pst_folders(...)` | folders | folder hierarchy |
| `read_pst_messages(...)` | all | base `IPM.Note` projection |
| `read_pst_notes(...)` | `IPM.Note` | notes only |
| `read_pst_contacts(...)` | `IPM.Contact` | contact-specific fields |
| `read_pst_distribution_lists(...)` | `IPM.DistList` | member lists |
| `read_pst_appointments(...)` | `IPM.Appointment` | calendar |
| `read_pst_sticky_notes(...)` | `IPM.StickyNote` | sticky notes |
| `read_pst_tasks(...)` | `IPM.Task` | task fields |

## Function parameters

Every reader accepts:

| Parameter | Default | Purpose |
|---|---|---|
| `read_body_size_bytes` | `1000000` | Truncate body/body_html. Set to `0` to read all. |
| `read_attachment_body` | `false` | Read attachment bytes into `a.bytes`. |
| `read_limit` | `NULL` | Cap total items, stops filesystem crawl early. |
| `planning_concurrency` | max | Async tasks during planning. Lower if `ulimit` complains. |

## Identifying a record

`node_id` is unique within one PST file, not globally. Always identify a record by the pair `(pst_path, node_id)`.

## Common queries

```sql
-- Top senders.
SELECT sender_email_address, count(*) AS n
FROM read_pst_messages('data/*.pst')
GROUP BY 1 ORDER BY 2 DESC LIMIT 20;

-- Attachments by extension.
SELECT lower(regexp_extract(a.filename, '\.([^.]+)$', 1)) AS ext, count(*)
FROM read_pst_messages('data/*.pst') m, UNNEST(m.attachments) t(a)
WHERE a.filename IS NOT NULL
GROUP BY 1 ORDER BY 2 DESC LIMIT 20;

-- Traffic per month.
SELECT date_trunc('month', message_delivery_time) AS mo, count(*)
FROM read_pst_messages('data/*.pst')
WHERE message_delivery_time IS NOT NULL
GROUP BY 1 ORDER BY 1;

-- Export one mailbox to Parquet.
COPY (
  SELECT pst_path, node_id, message_delivery_time,
         sender_name, sender_email_address, subject, body
  FROM read_pst_messages('/path/to/mailbox.pst', read_body_size_bytes := 0)
) TO 'mailbox.parquet' (FORMAT parquet);
```

## Debugging

```sql
CALL enable_logging(level = 'debug');
SELECT * FROM duckdb_logs();
```

Large mailbox trees can trip your `ulimit -n`. Lower `planning_concurrency`, or set `SET threads = n`.

## Performance

The extension supports projection pushdown and late materialization. Ask only for the columns you need. `read_body_size_bytes := 0` and `read_attachment_body := true` cost I/O only when the query actually projects those columns.

## Late materialization

`LIMIT`, Top-N, and sampling plans can enable late materialization. DuckDB evaluates the columns needed for filtering and ordering first, then reads other projected fields only for the selected rows. This is useful when filtering on inexpensive metadata while projecting fields such as `body`.

A `LIMIT` does not make a body predicate cheap: `WHERE body ILIKE ...` must still decode bodies to evaluate the filter.

```sql
-- Reads body for every matching row.
SELECT pst_path, node_id, conversation_topic, body
FROM read_pst_messages('data/*.pst', read_body_size_bytes := 0)
WHERE conversation_topic LIKE '%EnronOnline%';

-- Eligible for late materialization: filter and order on metadata, then read
-- bodies for the ten selected rows.
SELECT pst_path, node_id, conversation_topic, body
FROM read_pst_messages('data/*.pst', read_body_size_bytes := 0)
WHERE conversation_topic LIKE '%EnronOnline%'
ORDER BY creation_time
LIMIT 10;
```

If every matching row is required, omit the limit and accept the full projection cost. When an inexpensive first-stage query can identify records, materialize stable `(pst_path, node_id)` pairs and use them to fetch expensive fields in a second query.

**Verify LM is active** with `EXPLAIN` — look for a `HASH_JOIN` (Join Type SEMI) with `__node_id` and `__partition` as the conditions, and `READ_PST_*` scan children:

```
┌─────────────┴─────────────┐
│         HASH_JOIN         │
│      Join Type: SEMI      │
│        Conditions:        │
│   __node_id = __node_id   │
│ __partition = __partition │
└─────────────┬─────────────┘
```

If that node is absent, late materialization did not fire.

### Virtual columns

Two hidden columns are available on every table function. They are not in the default projection but can be filtered on directly:

| Column | Type | Use |
|---|---|---|
| `__node_id` | `UINTEGER` | Duplicate of `node_id`. Filtering on `__node_id IN (…)` pushes an efficient node-targeted scan when you already know the NIDs. |
| `__partition` | `UBIGINT` | Non-deterministic partition assigned during planning. Not for user filters, this is what the LM optimizer joins on. |

```sql
-- Target two known messages in one PST. Replace the path and IDs.
SELECT pst_path, subject, body
FROM read_pst_messages('/path/to/mailbox.pst', read_body_size_bytes := 0)
WHERE __node_id IN (12345, 67890);
```

Use this pattern to fetch full bodies for a small set of known records. Run a separate query for each PST because node IDs are file-local.

## Enron FERC corpus specifics

If the user has this dataset on disk (typically `enron-ferc-pst-v*` or the current release):

- 171 PST files, one or more per custodian
- ~1.23M messages, ~454K retained attachments
- Total on-disk ~78 GB
- Custodian mailboxes named `zl_<lastname>-<initial>_NNN.pst` (Enron reference `zl_` prefix)
- Removal policy and full change log: `README.md` and `CHANGELOG.md` in the dataset root

Example: list custodians and their message counts:

```sql
SELECT split_part(regexp_extract(pst_path, '/([^/]+\.pst)$', 1), '_', 2) AS custodian,
       count(*) AS n
FROM read_pst_messages('data/*.pst')
GROUP BY 1 ORDER BY 2 DESC;
```