duckdb-pdf

KO | EN

gitlite — search

#sql #gpt #json #vue #cloud-native #security #ai-agents #git #nextjs #docker

(★ 9)

Read and extract content from PDF files in DuckDB — Poppler (text/words/lines/tables/metadata) + Tesseract OCR. A DuckDB community extension.

File Explorer

ZIP Download

- - MainDistributionPipeline.yml
- UPDATING.md
- - pipeline.sql
  - README.md
- rag_pdf_vss.sql
- description.yml
- embed_base14_fonts.py
- embed_tessdata.py
- extension-upload.sh
- gen_signed_pdf.py
- test_font_starved_redact.sh
- - base14_fonts.hpp
  - pdf_extension.hpp
- ocr_ops.cpp
- ocr_ops.hpp
- pdf_extension.cpp
- poppler_private_ops.cpp
- qpdf_ops.cpp
- qpdf_ops.hpp
- - blank_split.pdf
  - financial_right_aligned.pdf
  - form_annots.pdf
  - garbage_header.pdf
  - gdocs_lists.pdf
  - gen_blank_split.py
  - gen_gdocs_lists.py
  - gen_images_pdf.py
  - gen_incremental_pdf.py
  - gen_markdown_fixture.c
  - gen_pdfa.py
  - gen_redact.py
  - gen_sign_cert.py
  - gen_skewed_scan.py
  - gen_table_fixtures.c
  - hello.pdf
  - images.pdf
  - incremental.pdf
  - markdown_fixture.pdf
  - pdfa.pdf
  - redact_secret.pdf
  - ruled_lattice.pdf
  - running_header.pdf
  - scanned.pdf
  - sign_cert.pem
  - sign_key.pem
  - sign_key_encrypted.pem
  - signed.pdf
  - signed_fixture_cert.pem
  - signed_fixture_key.pem
  - signed_tampered.pdf
  - skewed_scan.pdf
  - table.pdf
  - text_as_pdf.pdf
  - truncated.pdf
  - two_pages.pdf
  - zero.pdf
- - copy_pdf_options.test
  - copy_to_pdf.test
  - pdf_blob.test
  - pdf_chunks.test
  - pdf_full_api.test
  - pdf_images.test
  - pdf_inspect.test
  - pdf_lowlevel_api.test
  - pdf_ocr_bundled_eng.test
  - pdf_ocr_missing_model.test
  - pdf_ocr_preprocess.test
  - pdf_pdfa.test
  - pdf_redact.test
  - pdf_redact_lateral.test
  - pdf_revisions.test
  - pdf_scalars.test
  - pdf_sign.test
  - pdf_signatures.test
  - pdf_split_blank.test
  - pdf_to_markdown.test
  - pdf_to_pdf.test
  - pdf_to_png.test
  - pdf_watermark.test
  - pdf_write_page_images.test
  - qpdf_ops.test
  - qpdf_suite.test
  - read_pdf_basic.test
  - read_pdf_elements.test
  - read_pdf_ignore_errors.test
  - read_pdf_layout.test
  - read_pdf_lines.test
  - read_pdf_meta.test
  - read_pdf_ocr.test
  - read_pdf_ocr_words.test
  - read_pdf_page_parallel.test
  - read_pdf_parallel_scan.test
  - read_pdf_tables.test
  - read_pdf_text_layer.test
  - read_pdf_validation.test
  - read_pdf_words.test
  - torture_concurrency.test
  - torture_copy_pdf.test
  - torture_idempotency.test
  - torture_malformed.test
  - torture_markdown.test
  - torture_named_params.test
  - torture_roundtrip.test
  - write_pdf.test
- README.md
- - COPYING
  - d050000l.pfb
  - LICENSE
  - n019003l.pfb
  - n019004l.pfb
  - n019023l.pfb
  - n019024l.pfb
  - n021003l.pfb
  - n021004l.pfb
  - n021023l.pfb
  - n021024l.pfb
  - n022003l.pfb
  - n022004l.pfb
  - n022023l.pfb
  - n022024l.pfb
  - README.md
  - README.upstream.md
  - s050000l.pfb
- - eng.traineddata
  - LICENSE.tessdata_fast
.clang-format
.clang-tidy
.editorconfig
.gitattributes
.gitignore
.gitmodules
CMakeLists.txt
COOKBOOK.md
duckdb
extension-ci-tools
extension_config.cmake
LICENSE
Makefile
README.md
ROADMAP.md
vcpkg.json

# Use via CDN

jsDelivr

jsDelivr serves any public GitHub repository as a CDN with zero setup. Pick a version and a file to get a ready-to-paste link and snippet.

Version

File

Link

Example

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

**Everything PDF, in SQL.** A DuckDB community extension (`pdf`) that turns PDFs into tables and tables into PDFs: read text at every grain (page, line, word, layout element, retrieval chunk), inspect metadata / outlines / attachments / form fields / annotations / revisions / digital signatures, and do the everyday document operations people open Adobe Acrobat for — merge, split, rotate, compress, encrypt, decrypt, extract pages, write new PDFs. Built on [Poppler](https://poppler.freedesktop.org/) (parsing/rendering), [Tesseract](https://github.com/tesseract-ocr/tesseract) (OCR for scanned documents), [qpdf](https://qpdf.sourceforge.io/) (document surgery), and [libharu](https://github.com/libharu/libharu) (native PDF writing) — all in the DuckDB process, no external tools required at runtime.

Every function accepts a single path **or a glob**, so the natural unit of work is a folder of PDFs:

```sql
INSTALL pdf FROM community; LOAD pdf;

-- census a folder: producer, page count, size, encryption — one row per file
SELECT file, producer, page_count, is_encrypted, file_size
FROM pdf_info('docs/*.pdf') ORDER BY file_size DESC;

-- full-text search the folder, with page numbers
SELECT filename, page, text FROM read_pdf('docs/*.pdf') WHERE text ILIKE '%revenue%';

-- merge the folder into one PDF, then structurally compress it
SELECT pdf_compress(
         pdf_merge(list(file ORDER BY file), 'combined.pdf'),
         'combined_small.pdf')
FROM pdf_info('docs/*.pdf');

-- chunk everything for retrieval — one statement, section-aware
CREATE TABLE chunks AS FROM pdf_chunks('docs/*.pdf');
```

> **See [COOKBOOK.md](COOKBOOK.md) for task-oriented recipes** — RAG ingestion,
> full-text search, scanned-invoice OCR, quarantining broken files, signature
> forensics, bulk document surgery, thumbnails, and reporting. This README is the
> function-by-function reference.

## Installation

### Supported DuckDB host

**DuckDB v1.5.5 only** — linux_amd64/arm64, osx_amd64/arm64, windows_amd64.

### Community (signed)

```sql
INSTALL pdf FROM community;
LOAD pdf;
```

Requires **DuckDB v1.5.5**. Or:

```sql
INSTALL pdf FROM 'https://community-extensions.duckdb.org';
LOAD pdf;
```

### GitHub Release (offline / unsigned)

```text
pdf-duckdb-v1.5.5-linux_amd64.duckdb_extension
pdf-duckdb-v1.5.5-windows_amd64.duckdb_extension
pdf-duckdb-v1.5.5-osx_arm64.duckdb_extension
…
```

```sh
curl -fsSL -o pdf.duckdb_extension \
  "https://github.com/asubbarao/duckdb-pdf/releases/download/<tag>/pdf-duckdb-v1.5.5-osx_arm64.duckdb_extension"
duckdb -unsigned -c "LOAD '$(pwd)/pdf.duckdb_extension';"
```

## Read & extract

Six table functions read a PDF at six grains, plus scalar converters for whole-document output. All of them take a single path or a glob; the extraction never leaves the DuckDB process.

Common named parameters for `read_pdf`, `read_pdf_lines`, `read_pdf_words`, and `read_pdf_tables`:

| Parameter | Type | Default | Description |
|---|---|---|---|
| `password` | VARCHAR | — | Password for encrypted files. |
| `first_page` / `last_page` | INTEGER | full document | 1-based inclusive page range. |
| `layout` | VARCHAR | `'reading'` | Text extraction order: `'reading'`, `'physical'` (preserves column alignment), or `'raw'` (content-stream order). |
| `parse_tables` | BOOLEAN | false | Force physical layout (column-preserving) extraction. |
| `ocr` | BOOLEAN | false | Force OCR on every page, even pages with a text layer. |
| `auto_ocr` | BOOLEAN | true | OCR pages that have no extractable text layer. |
| `ocr_language` | VARCHAR | `'eng'` | Tesseract language model. |
| `ocr_dpi` | INTEGER | 300 | Render DPI for the OCR raster (1–2400). |
| `ocr_psm` / `ocr_oem` | INTEGER | Tesseract defaults | Page segmentation mode / OCR engine mode. |
| `ocr_preprocess` | BOOLEAN | true | Run the Leptonica preprocessing pipeline (grayscale → deskew → binarize → despeckle) on the rendered page before OCR. |
| `ocr_retry` | BOOLEAN | true | Re-render low-confidence pages (mean confidence < 55) at 2× DPI when the render was below 400 DPI, keeping whichever pass scores higher. |
| `tessdata_dir` | VARCHAR | auto-detected | Explicit Tesseract model directory (see [OCR support](#ocr-support)). |
| `ocr_vars` | MAP(VARCHAR, VARCHAR) | — | Tess `SetVariable` key/values applied after Init (same surface as CLI `-c name=value`). |
| `ocr_config` | VARCHAR | — | Path or tessdata config name for `ReadConfigFile` (applied after Init, before `ocr_vars`). |
| `ignore_errors` | BOOLEAN | false | `read_pdf` / `read_pdf_meta` only: skip unopenable files in a multi-file scan instead of aborting it. |

`read_pdf_elements` and `pdf_chunks` accept only `password`, `first_page`, `last_page` (plus `pdf_chunks`' own `chunk_size` / `overlap`) — they read the native text layer only.

### `read_pdf` — one row per page

Columns: `filename`, `page`, `page_count`, `text`, `width`, `height` (page size in PDF points), `has_text_layer` (native embedded text is non-blank), `used_ocr` (Tesseract produced the returned text), `ocr_confidence` (Tess MeanTextConf 0–100 when `used_ocr`, else NULL).

`has_text_layer` describes the PDF itself (true even when `ocr:=true` forced a re-OCR of a text page). `used_ocr` is true when the page had no extractable text and auto-OCR ran, or when `ocr:=true` forced OCR and the engine returned text. Together they make image-only vs embedded-text detection first-class:

```sql
-- Find scanned / image-only pages across a folder
SELECT filename, page FROM read_pdf('docs/*.pdf', auto_ocr:=false)
WHERE NOT has_text_layer;

-- Same pages, with OCR text filled in
SELECT filename, page, text FROM read_pdf('docs/*.pdf')
WHERE used_ocr;
```

The multi-file scan is **parallel**: each worker thread takes whole files, so a glob over a folder uses all your cores. (On Windows the scan runs serially — Poppler is not thread-safe across documents there.)

By default one corrupt or password-protected file aborts the whole scan. Pass `ignore_errors := true` (also on `read_pdf_meta`) to skip unopenable files and keep the good ones; the skipped set stays recoverable in SQL:

```sql
-- Which files were skipped?
SELECT file FROM glob('docs/*.pdf')
EXCEPT
SELECT DISTINCT filename FROM read_pdf('docs/*.pdf', ignore_errors := true);
```

```sql
-- Read all pages of a single PDF
SELECT page, text FROM read_pdf('report.pdf');

-- Search a folder of PDFs — parallel across files
SELECT filename, page, text
FROM read_pdf('docs/*.pdf')
WHERE text ILIKE '%revenue%';

-- Count pages across a set of files
SELECT filename, count(*) AS page_count
FROM read_pdf('invoices/*.pdf')
GROUP BY filename;
```

### `read_pdf_lines` — one row per line

Columns: `filename`, `page`, `line` (1-based, reset per page), `text`. A PDF-aware analog of `read_lines`: grep a PDF the way you'd grep a text file, keeping page + line context.

```sql
SELECT page, line, text
FROM read_pdf_lines('report.pdf')
ORDER BY page, line;

-- Grep across a directory of PDFs, keeping line context
SELECT filename, page, line, text
FROM read_pdf_lines('invoices/*.pdf')
WHERE text ILIKE '%total due%';
```

### `read_pdf_words` — one row per word, with bounding boxes

Columns: `filename`, `page`, `word`, `x0`, `y0`, `x1`, `y1` (PDF points; poppler-cpp `text_list` y is top-down on typical pages — smaller `y0` is higher on the page), `font_name`, `font_size`, `source`, `confidence`, **`line`** (1-based geometric line id within the page), **`page_width`** / **`page_height`** (crop-box size in points for that page — same numbers as `read_pdf.width` / `height`).

`line` clusters words with the same vertical-overlap rule as `read_pdf_elements` (≥ 50% of the shorter word height). Use it to equi-join marks to line context or rebuild lines in one Poppler walk:

```sql
-- Line grain derived from the word pin (no second read_pdf_lines walk)
SELECT filename, page, line,
       string_agg(word, ' ' ORDER BY x0) AS text
FROM read_pdf_words('docs/*.pdf')
GROUP BY filename, page, line
ORDER BY filename, page, line;
```

`page_width` / `page_height` remove the common max-bbox geometry hack (`max(x1)`, `max(y1)`). For page-only census without extracting words, prefer `pdf_pages_info` (media/crop/rotation/label) or `read_pdf` / `pdf_info`.

For scanned/image-only pages the words come from Tesseract's word-level iterator: `source` is `'ocr'` (vs `'text'` for native words) and `confidence` is a value in `[0, 100]` (`NULL` for native words). OCR words have `NULL` `font_name`/`font_size` — there is no font metadata in an image. OCR words also get `line` / page size.

```sql
-- All words with coordinates + line association
SELECT page, line, word, x0, y0, x1, y1, page_width, page_height
FROM read_pdf_words('report.pdf')
ORDER BY page, line, x0;

-- Words on the first geometric line of each page
SELECT page, word, x0
FROM read_pdf_words('report.pdf')
WHERE line = 1
ORDER BY page, x0;

-- OCR word boxes from a scanned document
SELECT word, x0, y0, x1, y1, confidence, line
FROM read_pdf_words('scan.pdf')
WHERE source = 'ocr'
ORDER BY page, line, x0;
```

> **Note:** `read_pdf_lines` still splits Poppler `page->text()` on newlines (layout/`physical`/`raw` aware). Geometric `line` on words is the join key for bbox work; text-split line numbers may differ on multi-column or unusually spaced pages. Prefer one pin: words (+ optional `string_agg` lines) when you need both grains.

### `read_pdf_tables` — structured table extraction

Columns: `filename`, `page`, `table_index`, `row_index`, `cells` (a `VARCHAR[]` of the row's cells). Works on scanned PDFs too, via the OCR word layer.

Two detectors run per page:

- **Lattice (ruled) tables** — horizontal/vertical rule segments are collected from the page content streams and, when they form a usable grid, used as authoritative cell separators. Bordered tables are cut exactly along their lines, including cells whose text alignment alone would mis-cluster.
- **Unruled tables** — a precision-first geometric heuristic (word bounding-box column clustering with a regularity gate, plus document-wide column-edge voting so **right-aligned numeric columns** cluster correctly). It favors not emitting spurious tables from prose over catching every table.

Merged cells and borderless/sparse tables remain out of scope — see [Scope](#scope).

```sql
-- Extract all tables from a PDF
SELECT * FROM read_pdf_tables('financial_report.pdf');

-- Combine tables across a folder
SELECT filename, page, table_index, row_index, cells
FROM read_pdf_tables('docs/*.pdf')
ORDER BY filename, page, table_index, row_index;
```

### `read_pdf_elements` — layout elements in reading order

Columns: `file`, `page_number` (1-based), `element_idx` (1-based within page), `element_type`, `text`, `font_size` (dominant size of the element, `NULL` when the PDF carries no font info), `bbox_x0`, `bbox_y0`, `bbox_x1`, `bbox_y1`.

Elements are built by deterministic geometry over Poppler's positioned word list — words cluster into lines by vertical overlap, lines into blocks by vertical gap (> 0.6× median line height), font-size change (> 15%), or a list marker starting a line. Classification: `heading` = dominant font ≥ 1.15× the document's modal body size and < 200 chars, OR a short ALL-CAPS block at any font size; `list_item` = first line starts with a bullet glyph (`• – ▪ ● ○ ◦ ∙ ‣ ⁃ · ▸`, or `- ` / `* `) or an `N.` / `N)` marker (zero-width format chars after the marker — Google Docs `●<ZWSP>` / `1.<ZWSP>` — are skipped); `paragraph` = any other block of ≥ 3 words; `other` = the rest (page numbers, isolated fragments, and **running headers/footers**). A heading instance that sits in the top or bottom 12% of the page is demoted to `other` when the same text occupies that band on ≥ 5 pages (even/odd running titles). A mid-page cover byline with the same words stays a heading. Reads the native text layer only (no OCR) and does not attempt table detection — use `read_pdf_tables` for tables.

```sql
-- Document outline: just the headings
SELECT page_number, text, font_size
FROM read_pdf_elements('report.pdf')
WHERE element_type = 'heading'
ORDER BY page_number, element_idx;

-- Paragraph-grain extraction across a folder, keeping position
SELECT file, page_number, element_idx, text, bbox_y0
FROM read_pdf_elements('docs/*.pdf')
WHERE element_type IN ('paragraph', 'list_item')
ORDER BY file, page_number, element_idx;
```

### `pdf_chunks` — retrieval-ready chunking

`pdf_chunks(files, chunk_size := 1200, overlap := 150)` packs the `read_pdf_elements` grain into retrieval-ready chunks. Columns: `file`, `chunk_idx` (1-based per file), `text`, `page_start`, `page_end`, `n_chars` (length of the emitted text, overlap included), and `heading` (text of the nearest preceding `heading` element — every chunk carries its section context; `NULL` before the first heading).

Deterministic chunking rules (no ML, no tokenizers):

1. Elements pack into a chunk in reading order until the next one would push the chunk past `chunk_size`.
2. An element is never split across chunks — unless it alone exceeds `chunk_size`, in which case it splits at the last whitespace before the limit (repeatedly for very long elements).
3. A `heading` never ends a chunk: it glues forward to start the next one, keeping headers attached to their section.
4. Each chunk after the first is prefixed with the trailing `overlap` characters of the previous chunk's text, trimmed to a whitespace boundary so no word is cut.

```sql
-- One statement from a folder of PDFs to a chunk table
CREATE TABLE chunks AS FROM pdf_chunks('docs/*.pdf');

-- Every chunk knows its section
SELECT heading, chunk_idx, n_chars, text[:80]
FROM pdf_chunks('report.pdf', chunk_size := 800, overlap := 100);
```

### Whole-document scalars: `pdf_to_text` / `markdown` / `html` / `xml` / `svg` / `png`

Scalar converters return the whole document (or one rendered page) as a single value — useful in expressions, joins, and `COPY` pipelines.

| Function | Signature | Returns |
|---|---|---|
| `pdf_to_text` | `(path_or_blob [, layout])` | Plain text; `layout` is `'reading'`, `'physical'`, or `'raw'`. |
| `pdf_to_markdown` | `(path)` | GitHub-flavoured Markdown (headings, bold, bullets, pipe tables) — see below. Path form only. |
| `pdf_to_html` | `(path_or_blob)` | Absolutely-positioned HTML. |
| `pdf_to_xml` | `(path_or_blob)` | pdftoxml-style XML with per-word bounding boxes. |
| `pdf_to_svg` | `(path_or_blob, page [, dpi])` | SVG embedding a base64 PNG raster of the page. Default DPI 150, range 1–2400. |
| `pdf_to_png` | `(path_or_blob, page [, dpi])` | Raw PNG bytes as a `BLOB`. Same raster and DPI rules as `pdf_to_svg`. |
| `pdf_write_page_images` | `(path_or_glob, out_dir [, dpi, first_page, last_page, password])` | **Table** — write each page as a PNG under `out_dir/<stem>/p{N}.png` (1-based, no zero-pad). Returns `(file, page, out_path, width, height, bytes)`. Same raster + bundled base-14 fonts as `pdf_page_images` / `pdf_to_png`. |

**Paths go through DuckDB's FileSystem**, so `pdf_to_text('s3://...')` or `pdf_to_html('https://...')` work when `httpfs` (or another VFS) is loaded. **BLOB overloads** take the raw bytes directly — no filesystem involved:

```sql
-- Full text of a PDF as one value
SELECT pdf_to_text('report.pdf') AS full_text;

-- Physical layout preserves column alignment
SELECT pdf_to_text('report.pdf', 'physical');

-- Materialize a folder into a text table
CREATE TABLE doc_texts AS
SELECT file, pdf_to_text(file) AS body
FROM glob('docs/*.pdf');

-- BLOB roundtrip: bytes in, text out, no temp files
SELECT pdf_to_text(content) FROM read_blob('report.pdf');

-- Render page 1 as a PNG; DPI controls raster size
SELECT octet_length(pdf_to_png('report.pdf', 1))      AS png_150dpi,
       octet_length(pdf_to_png('report.pdf', 1, 300)) AS png_300dpi;

-- Write page previews to disk (replaces pdftoppm loops): pages/report/p1.png, …
SELECT file, page, out_path, width, height, bytes
FROM pdf_write_page_images('docs/*.pdf', 'pages', dpi := 100);
```

`pdf_to_markdown` converts using only Poppler's word-level geometry — no AI, no external tools, fully deterministic and local. It detects **headings** (font ≥ 1.15× body size, level by descending size rank), **tables** (aligned word columns emitted as GitHub pipe tables), **bold spans** (font name contains "Bold"), **lists** (same marker set as `read_pdf_elements`: `-` / `*` / `• – ▪ ● ○ ◦ ∙ ‣ ⁃ · ▸` / `N.` / `N)`, with Google Docs ZWSP skipped; unicode bullets emit as `- `), and **paragraphs** (consecutive same-indent lines merged). Pages are joined with `\n\n`; `NULL` input → `NULL` output; missing or encrypted files raise an error.

```sql
SELECT pdf_to_markdown('report.pdf') AS md;
```

### `pdf_page_images` — every page as a PNG BLOB (no `pdftoppm`)

Table function for **in-process page previews**: one row per page with a rendered PNG, so vision / llm pipelines and thumbnails never need the Poppler CLI (`pdftoppm`) or a shell loop. Path or glob; named parameters `password`, `dpi` (default 150, range 1–2400), `first_page`, `last_page`.

Columns: `file`, `page` (1-based), `page_count`, `dpi`, `width`, `height` (pixels from the PNG IHDR), `png` (`BLOB`).

```sql
-- BLOB per page — handoff to a vision model / llm extension
SELECT file, page, png FROM pdf_page_images('docs/*.pdf', dpi := 100);

-- Or the scalar for a single page
SELECT pdf_to_png('a.pdf', 1, 100);
```

**Static file trees** (`pages/<stem>/pN.png` for Closure-style UIs): use `pdf_write_page_images` (pdf ≥ 0.7.7) — writes the whole tree in one call, creates `out_dir` + stem dirs, same raster/fonts as above. See [COOKBOOK §7](COOKBOOK.md#7-pdf--images-and-thumbnails-no-pdftoppm).

```sql
SELECT file, page, out_path, width, height, bytes
FROM pdf_write_page_images('docs/*.pdf', 'pages', dpi := 100);
-- → pages/report/p1.png, pages/report/p2.png, …
```

**Fonts (pdf ≥ 0.7.3):** community binaries bundle URW **base-14** substitute fonts (Helvetica / Times / Courier / Symbol / ZapfDingbats) and register them with Poppler at load, so page rasters should not come back blank on hosts without fontconfig/system display fonts. Same path powers `pdf_redact`, OCR rendering, and `pdf_write_page_images`.

## Inspect

These table functions answer "what is in this file?" without extracting body text. All take a path or glob; `password := '...'` is accepted by `pdf_info`, `read_pdf_meta`, `pdf_outline`, `pdf_attachments`, `pdf_revisions`, `pdf_signatures`, and `pdf_images`.

### `pdf_info` — full per-file census

One row per file: identity metadata plus structural facts. Unset metadata keys and dates are `NULL`, never `''`. `width`/`height` are the first-page media box in points; `file_size` is bytes on disk.

Columns: `file`, `title`, `author`, `subject`, `keywords`, `creator`, `producer`, `creation_date` (`TIMESTAMP`), `mod_date` (`TIMESTAMP`), `page_count`, `is_encrypted`, `is_linearized`, `pdf_version`, `width`, `height`, `file_size`, `pdfa_part` (`INTEGER`), `pdfa_conformance` (`VARCHAR`).

`pdfa_part` and `pdfa_conformance` are read straight from the `pdfaid:part` / `pdfaid:conformance` claims in the document's XMP metadata packet — this is **detection only**, not validation. A file that merely declares `pdfaid:part=2, conformance=B` will report those values even if it does not actually conform to PDF/A. Both columns are `NULL` when the XMP packet is absent or carries no `pdfaid` claim.

```sql
-- Census a folder of PDFs
SELECT file, title, author, page_count, pdf_version, file_size
FROM pdf_info('docs/*.pdf')
ORDER BY file_size DESC;

-- Which tool produced what, and when
SELECT producer, count(*) AS n, min(creation_date) AS earliest
FROM pdf_info('docs/*.pdf')
GROUP BY producer;
```

### `pdf_pages_info` — per-page geometry

One row per page (not just first-page like `pdf_info.width`/`height`). Columns: `file`, `page`, `page_count`, `width`, `height` (crop box), `media_width`, `media_height`, `crop_width`, `crop_height`, `rotation` (0/90/180/270), `orientation`, `label`, `duration`. Prefer this when you need page size without extracting words; when you already pin `read_pdf_words`, use its `page_width`/`page_height` instead of a second open.

```sql
SELECT file, page, width, height, rotation, orientation
FROM pdf_pages_info('docs/*.pdf')
ORDER BY file, page;
```

### `read_pdf_meta` — legacy per-file metadata

The original metadata function, kept for compatibility. `pdf_info` is a superset (adds timestamps, linearization, page dimensions, file size); prefer it for new queries.

Columns: `filename`, `title`, `author`, `subject`, `keywords`, `creator`, `producer`, `pages`, `pdf_version`, `encrypted`.

```sql
SELECT filename, title, author, pages
FROM read_pdf_meta('report.pdf');
```

### `pdf_outline` — bookmarks / table of contents

One row per bookmark, in depth-first document order. Files without an outline yield zero rows (not an error). Columns: `file`, `ord` (1-based document order), `depth` (1 = top level), `title`.

```sql
-- Table of contents with indentation
SELECT file, ord, repeat('  ', depth - 1) || title AS entry
FROM pdf_outline('manual.pdf')
ORDER BY ord;
```

### `pdf_attachments` — embedded files

One row per embedded file; zero rows when there are none. `data` carries the attachment bytes as a `BLOB`; `size` is `NULL` when the PDF does not declare it. Columns: `file`, `name`, `description`, `size`, `mime_type`, `data`.

```sql
-- What is embedded across a folder?
SELECT file, name, mime_type, size
FROM pdf_attachments('invoices/*.pdf');
```

### `pdf_form_fields` — AcroForm fields

One row per form field. Columns: `file`, `page` (`NULL` if the field has no widget on any page), `field_name` (fully qualified), `field_type` (`'text'`/`'button'`/`'choice'`/`'signature'`, else qpdf's raw type name), `value` (`NULL` if unset), `is_required`.

```sql
-- Every field in a folder of forms
SELECT file, field_name, field_type, value, is_required
FROM pdf_form_fields('forms/*.pdf');
```

### `pdf_annotations` — annotations and hyperlinks

One row per page annotation. Columns: `file`, `page`, `subtype` (`Link`, `Highlight`, `Text`, `FreeText`, ...), `contents` (`NULL` if none), `uri` (populated for Link annotations with an `/A` `/URI` action), `rect_x0`, `rect_y0`, `rect_x1`, `rect_y1`.

```sql
SELECT page, subtype, contents FROM pdf_annotations('forms/*.pdf');

-- The hyperlink-extraction recipe
SELECT file, page, uri
FROM pdf_annotations('forms/*.pdf')
WHERE subtype = 'Link';
```

### `pdf_revisions` — incremental-update forensics

PDFs are append-only: editing a file with most tools adds an *incremental update* (a new body + xref + `%%EOF`) after the original bytes, leaving every earlier revision intact and recoverable. `pdf_revisions` enumerates those revisions by parsing the raw bytes (`startxref` / `%%EOF` markers and the trailer `/Prev` chain — classic xref tables and xref streams alike), oldest first.

One row per revision. Columns: `revision_index` (0 = original document), `startxref_offset`, `eof_offset` (byte offset just past this revision's `%%EOF`), `size_bytes` (bytes this revision added), `is_incremental` (`false` only for revision 0).

```sql
-- Has this contract been modified after it was first written?
SELECT count(*) - 1 AS updates_after_original
FROM pdf_revisions('contract.pdf');

-- Timeline of what each edit added
SELECT revision_index, size_bytes, eof_offset
FROM pdf_revisions('contract.pdf')
ORDER BY revision_index;
```

A single-revision file yields exactly one row. Truncating a file at any earlier `eof_offset` reconstructs that revision — useful for "what did this document say before the last edit?" forensics.

### `pdf_signatures` — digital signatures: detect + verify

One row per filled AcroForm signature field (unsigned files and empty signature fields yield zero rows, not an error). Columns: `file`, `field_name`, `subfilter` (e.g. `adbe.pkcs7.detached`), `signing_time` (`TIMESTAMP`), `signer_name`, `reason`, `location`, `covers_whole_file`, `verified`.

`verified` is a real cryptographic check: the detached CMS/PKCS#7 blob (`/Contents`) is verified with OpenSSL over the exact `/ByteRange` spans it signs. It attests **integrity** — the signed bytes have not been altered — but does not validate certificate-chain trust (no CA store is consulted). `covers_whole_file = false` means the file was extended after signing (see `pdf_revisions`): the signature can still verify over its own range while later revisions changed the visible document.

```sql
-- Are these contracts signed, by whom, and are the signatures intact?
SELECT file, signer_name, signing_time, verified, covers_whole_file
FROM pdf_signatures('contracts/*.pdf');

-- Signed-then-modified documents (signature valid but not covering the file)
SELECT file, field_name
FROM pdf_signatures('contracts/*.pdf')
WHERE verified AND NOT covers_whole_file;
```

### `pdf_images` — extract embedded raster images

One row per embedded image XObject per page — the **actual stored raster** (the JPEG a scanner wrote, the bitmap an editor placed), not a render of the page. Files with no images yield zero rows, not an error. Columns: `file`, `page` (1-based), `image_index` (1-based within page), `name` (resource name, e.g. `Im0`), `width`, `height`, `bits_per_component`, `colorspace` (raw PDF name, e.g. `DeviceRGB`), `format`, `data` (`BLOB`).

`format` tells you what `data` holds, and every value is directly usable:

| `format` | Source filter | `data` contents |
| --- | --- | --- |
| `jpeg` | `/DCTDecode` | Raw JPEG (JFIF) bytes, passed through (any earlier `/FlateDecode` is undone, the JPEG layer preserved). |
| `jp2` | `/JPXDecode` | Raw JPEG-2000 codestream. |
| `ccitt` | `/CCITTFaxDecode` | Raw fax-encoded bytes (qpdf does not decode CCITT). |
| `png` | `/FlateDecode`, `/RunLengthDecode`, `/LZWDecode`, none | Fully decoded samples re-wrapped as a PNG. Applies to `DeviceGray`/`CalGray` (1- or 8-bit) and `DeviceRGB`/`CalRGB` (8-bit). |
| `raw` | any of the above decodable filters | Decoded sample bytes with no container, used when the colorspace/bit depth can't be losslessly wrapped as PNG (`DeviceCMYK`, `Indexed`, `ICCBased`, image masks, exotic depths). |

```sql
-- Inventory every embedded image across a folder
SELECT file, page, image_index, width, height, colorspace, format,
       octet_length(data) AS bytes
FROM pdf_images('scans/*.pdf')
ORDER BY file, page, image_index;

-- Dump each image to its own file (extension follows the format)
COPY (
  SELECT data,
         format('extracted/{}_p{}_i{}.{}', regexp_replace(file, '.*/', ''),
                page, image_index,
                CASE format WHEN 'jpeg' THEN 'jpg' WHEN 'jp2' THEN 'jp2'
                            WHEN 'png' THEN 'png' ELSE 'bin' END) AS path
  FROM pdf_images('scans/invoice.pdf')
) TO 'extracted' (FORMAT parquet);  -- or iterate rows and write_blob(path, data)
```

The `png`/`jpeg` rows drop straight into DuckDB's image tooling or any downstream writer; `raw` rows need the `colorspace`/`bits_per_component`/`width`/`height` columns to interpret the sample bytes.

## Transform & write

Document-level operations powered by qpdf are content-preserving structural transforms — no rasterization, no re-typesetting; page content streams are carried over byte-identically. Conventions shared by all of them: **local filesystem paths exactly as given**; an existing output file is overwritten (`COPY TO` semantics); a missing output *directory* is an error, never created; in-place operation (`input == output`) is refused because qpdf reads the source lazily during the write. Scalars return the output path, which makes them composable — the output of one is the input of the next.

### `pdf_merge` / `pdf_split` / `pdf_rotate` / `pdf_pages`

```sql
-- merge: pages concatenate in list order
SELECT pdf_merge(['a.pdf', 'b.pdf', 'c.pdf'], 'combined.pdf');

-- the glob recipe: build the input list in SQL
SELECT pdf_merge(list(DISTINCT filename ORDER BY filename), 'combined.pdf')
FROM read_pdf('docs/*.pdf');

-- split: one single-page PDF per page -> out/<stem>_p<N>.pdf (N zero-padded
-- to the page-count width); returns one row per emitted file
SELECT page, file FROM pdf_split('combined.pdf', 'out');

-- rotate: degrees must be a multiple of 90 (added to each page's existing
-- rotation); pages is 'all' (default) or a qpdf-style range ('1-3,7', 'z' = last)
SELECT pdf_rotate('scan.pdf', 'upright.pdf', 90);
SELECT pdf_rotate('scan.pdf', 'upright2.pdf', -90, '1');

-- pages: extract a subset into a new document, in range order (qpdf range
-- grammar: '1-3,7', 'z' = last page, 'r2' = second-to-last; repeats allowed)
SELECT pdf_pages('report.pdf', 'summary.pdf', '1');
```

### `pdf_split_blank` — mailroom batch splitting on blank-page separators

An office scanner often produces ONE PDF that is really N documents separated by blank sheets dropped in as dividers. `pdf_split_blank` detects those separator pages (no extractable text **and** a (near-)white raster — text-empty alone would false-positive on a full-page-image scan) and writes each logical document to its own file, dropping the separators.

```sql
-- batch.pdf = [invoice A (2pp), blank, invoice B (1pp), blank, blank, invoice C (1pp)]
-- -> 3 documents written to out/<stem>_doc<K>.pdf; separators are dropped, never emitted as empty docs
SELECT document, first_page, last_page, page_count, file
FROM pdf_split_blank('batch.pdf', 'out');

-- tune sensitivity: raise blank_threshold to require an even purer white page
-- before treating it as a separator (default 0.995 = 99.5% near-white pixels)
SELECT * FROM pdf_split_blank('batch.pdf', 'out', blank_threshold := 0.999);
```

### `pdf_compress` / `pdf_encrypt` / `pdf_decrypt`

```sql
-- compress: object-stream generation + stream recompression + linearization
-- ("fast web view"). This optimizes document STRUCTURE and stream encoding;
-- it does NOT downsample or re-encode images — a scan-heavy PDF will not
-- shrink much.
SELECT pdf_compress('report.pdf', 'report_small.pdf');

-- encrypt: AES-256 (R6, the only password scheme the PDF 2.0 spec still
-- endorses), all permissions allowed. Omitted owner password falls back to
-- the user password. Uses qpdf's built-in crypto — no external library.
SELECT pdf_encrypt('report.pdf', 'report_locked.pdf', 'user-secret');
SELECT pdf_encrypt('report.pdf', 'report_locked2.pdf', 'user-secret', 'owner-secret');

-- decrypt: open with the (user or owner) password, write a plain copy.
-- A wrong password raises an Invalid Input error ("invalid password").
SELECT pdf_decrypt('report_locked.pdf', 'report_plain.pdf', 'user-secret');
```

Encrypted files stay readable in place, too — every reader takes `password := '...'`:

```sql
SELECT page, text FROM read_pdf('report_locked.pdf', password := 'user-secret');
```

### `pdf_watermark` / `pdf_bates`

Both stamp **real, selectable text** on top of every page (a Helvetica text run appended to each page's content stream), so the stamp survives into the extracted text layer and is searchable — not a flattened raster. Each page is sized and positioned from its own MediaBox, so mixed-size documents stamp correctly.

```sql
-- watermark: a large 45° diagonal gray "DRAFT" centered across every page.
-- 3-arg form uses the default opacity (0.30); the 4-arg form takes an explicit
-- fill alpha in (0, 1].
SELECT pdf_watermark('report.pdf', 'report_draft.pdf', 'DRAFT');
SELECT pdf_watermark('report.pdf', 'report_faint.pdf', 'CONFIDENTIAL', 0.15);

-- bates: legal-style Bates numbering — prefix + a zero-padded (min 6 digits)
-- sequential number at the bottom-right of each page, incrementing per page.
-- Page 1 -> ACME000100, page 2 -> ACME000101, ...
SELECT pdf_bates('deposition.pdf', 'deposition_stamped.pdf', 'ACME', 100);
```

### `pdf_sign` — apply a digital signature

`pdf_sign(input, output, cert_path, key_path)` adds an `adbe.pkcs7.detached` PKCS#7/CMS signature and writes the signed document to `output`. It is the inverse of `pdf_signatures` (above): the two form a closed loop — anything `pdf_sign` writes verifies back through `pdf_signatures` with `covers_whole_file = true` and `verified = true`. It is a single-row table function returning `{output, field_name}`.

`cert_path` and `key_path` are **PEM** files — an X.509 certificate and its RSA/EC private key (PKCS#8 or PKCS#1). A self-signed pair is fine; the signature attests integrity, not certificate-chain trust (mirroring what `pdf_signatures` checks). Generate one with:

```sh
openssl genrsa -out key.pem 2048
openssl req -new -x509 -key key.pem -out cert.pem -days 3650 -subj '/CN=Your Name'
```

Named options: `key_password` (decrypts an encrypted private key), `reason`, `location`, `signer_name` (populate the `/Reason` `/Location` `/Name` dictionary keys), `field_name` (the signature field name, default `'Signature1'`), and `password` (open an encrypted input PDF).

```sql
-- sign, then verify the closed loop with pdf_signatures
SELECT output FROM pdf_sign('contract.pdf', 'contract_signed.pdf', 'cert.pem', 'key.pem',
                            reason := 'I approve this document', location := 'San Francisco',
                            signer_name := 'Jane Doe');

SELECT field_name, signer_name, reason, covers_whole_file, verified
FROM pdf_signatures('contract_signed.pdf');
--   Signature1 | Jane Doe | I approve this document | true | true

-- encrypted private key: supply its passphrase
SELECT output FROM pdf_sign('contract.pdf', 'contract_signed.pdf', 'cert.pem', 'enc_key.pem',
                            key_password := 'secret');
```

The signature covers the whole file via a `/ByteRange` around a fixed-size `/Contents` hole (8 KB of DER — ample for RSA-2048); an oversized signature raises an Invalid Input error. Standard `pdf_encrypt`/`pdf_decrypt` conventions apply: local paths, output overwritten, `input == output` refused.

### `pdf_redact` — true (raster) redaction

`pdf_redact(input, output, redactions [, dpi := 200, password := '...'])` performs **true removal**, not a black box drawn over live text. Every page carrying at least one redaction box is re-rendered to an RGB raster, the boxes are painted **solid black in the pixels**, and the page is rebuilt as an **image-only page** whose entire content is that raster. Because the page no longer contains any text object, the redacted text is **permanently unextractable** — `read_pdf` returns nothing for it, and there is no hidden text layer to recover (unlike a vector rectangle drawn on top of still-selectable text). Pages with no boxes are copied through byte-for-byte and keep their live text. It returns one row per output page: `page INTEGER`, `redacted BOOLEAN`, `boxes_applied INTEGER`.

`redactions` is a `LIST(STRUCT(page INTEGER, x DOUBLE, y DOUBLE, w DOUBLE, h DOUBLE))`. **Coordinate convention:** all values are in **PDF points** (1 pt = 1/72 in) with the **origin at the page's bottom-left corner** (standard PDF user space). `(x, y)` is the **lower-left corner** of the box; `w`/`h` are its width and height. `page` is **1-based**. A box that runs off the page is clamped to the raster.

`dpi` (default 200) sets the render resolution of the rebuilt pages: higher dpi preserves more visual fidelity of the surrounding (unredacted) content but produces a larger output file; lower dpi is smaller but coarser. It does not affect the redaction guarantee — the covered text is gone at any dpi. Because redacted pages become images, their text is no longer selectable or searchable; leave unredacted pages untouched to preserve their text layer.

**Column-ref / row-mode:** use the separate pure in-out function `pdf_redact_lateral` (same two-name pattern as `read_lines` / `read_lines_lateral`). `input`, `output`, and `redactions` are **column references** (one set of values per outer row). A plain dependent join expands each document to its per-page result rows — no `format()` / `query()` string-building:

```sql
SELECT d.id, r.page, r.redacted, r.boxes_applied
FROM documents d
CROSS JOIN pdf_redact_lateral(d.source_path, d.out_path, d.boxes) r;
```

`pdf_redact` remains the classic constant-arg form with named parameters `dpi` / `password`. Named parameters do not work on in-out functions; `pdf_redact_lateral` is positional-only and uses defaults `dpi = 200` and `password = ''`.

```sql
-- Redact one box over sensitive text on page 2 (origin bottom-left, points).
-- Page 2 becomes an image; pages 1 and 3 keep their selectable text.
SELECT page, redacted, boxes_applied
FROM pdf_redact('statement.pdf', 'statement_redacted.pdf',
                [{'page': 2, 'x': 72.0, 'y': 590.0, 'w': 260.0, 'h': 40.0}]);
-- ┌──────┬──────────┬───────────────┐
-- │ page │ redacted │ boxes_applied │
-- ├──────┼──────────┼───────────────┤
-- │    1 │ false    │             0 │
-- │    2 │ true     │             1 │
-- │    3 │ false    │             0 │
-- └──────┴──────────┴───────────────┘

-- The redacted text is gone — this returns 0 rows:
SELECT * FROM read_pdf('statement_redacted.pdf') WHERE text LIKE '%account number%';
```

### `write_pdf` — native text-to-PDF (no LibreOffice)

`write_pdf(content [, output_path])` is a purely native, in-process scalar that authors a PDF from a VARCHAR using libharu — no external tools, no shell-out, always available once the extension is loaded. The 1-arg form writes to a temp file (platform `TMPDIR`/`TMP`/`TEMP` or `/tmp` + UUID); both forms return the output path.

```sql
-- 1-arg: temp file; 2-arg: explicit path
SELECT write_pdf('Line one.' || chr(10) || 'Line two.');
SELECT write_pdf('Hello native PDF from DuckDB.', 'out.pdf');

-- NULLs propagate
SELECT write_pdf(NULL);        -- NULL
SELECT write_pdf('x', NULL);   -- NULL

-- Self-contained roundtrip: read -> edit in SQL -> write -> read, all in-process
SELECT write_pdf(replace(pdf_to_text('report.pdf'), 'Q3', 'Q4'), 'report_q4.pdf');
SELECT page, text FROM read_pdf('report_q4.pdf');
```

Behavior and limits: Letter page size, 0.75 in margins, Helvetica 10 pt; splits on `\n`, expands tabs, word-wraps each logical line, auto-paginates; empty content produces a single blank (but valid) page. Only Base-14 Helvetica (WinAnsi/Latin-1 encoding) — non-Latin scripts and many UTF-8 glyphs will not render; use `to_pdf` + rich HTML for international text. The output path must be writable; failures raise a clear `IOException`.

### `COPY ... TO ... (FORMAT pdf)`

Write any query result directly to a PDF. Each result row is emitted as one text line with columns space-separated, typeset exactly as `write_pdf` (word-wrapped, paginated); row order is preserved (the copy runs serially).

```sql
COPY (SELECT page, text FROM read_pdf('report.pdf')) TO 'copy_out.pdf' (FORMAT pdf);
SELECT page, text FROM read_pdf('copy_out.pdf');
```

Supported options (case-insensitive; unknown options raise a binder error):

| Option | Type | Default | Description |
|--------|------|---------|-------------|
| `TITLE` | VARCHAR | (none) | PDF document metadata Title. |
| `AUTHOR` | VARCHAR | (none) | PDF document metadata Author. |
| `FONT_SIZE` | DOUBLE | 10 | Body font size in points (4–72). |
| `PAGE_SIZE` | VARCHAR | `'letter'` | `'letter'`, `'a4'`, or `'legal'`. |
| `MARGIN` | DOUBLE | 54 | Margin in points (0–216). |
| `HEADER` | VARCHAR | (none) | Centered line in the top margin band of every page (font_size−2, min 6). |
| `FOOTER` | VARCHAR | (none) | Centered line in the bottom margin band; supports a literal `{page}` placeholder. |

If `HEADER` or `FOOTER` is supplied, `MARGIN` must be ≥ 24 (to fit the band).

```sql
COPY (SELECT file, page_count, file_size FROM pdf_info('docs/*.pdf') ORDER BY file)
TO 'census.pdf' (FORMAT pdf,
                 TITLE 'Folder census',
                 AUTHOR 'duckdb-pdf',
                 PAGE_SIZE 'a4',
                 FONT_SIZE 11,
                 HEADER 'Internal',
                 FOOTER 'confidential - page {page}');
```

### `to_pdf` — office documents to PDF (LibreOffice)

`to_pdf(input_path [, output_path])` converts a rich document (`docx`, `odt`, `rtf`, `html`, `pptx`, `xlsx`, ...) to PDF and returns the output path. Unlike everything else in this extension, **`to_pdf` shells out to LibreOffice at runtime** — it is not a build/link dependency, so the extension installs and loads fine without LibreOffice; the function errors with an actionable message only when called with no converter present. The 1-arg form writes next to the input with the extension swapped to `.pdf`.

```bash
# Install LibreOffice once
brew install --cask libreoffice                 # macOS
sudo apt-get install libreoffice                # Debian / Ubuntu
# Windows: https://www.libreoffice.org/download
```

```sql
-- Convert next to the input; returns the new path
SELECT to_pdf('resume.docx');           -- -> 'resume.pdf'

-- ... then read the produced PDF straight back
SELECT page, text FROM read_pdf('resume.pdf');
```

The `soffice`/`libreoffice` binary is resolved in this order: **`LIBREOFFICE_PATH` env var → `soffice` on `$PATH` → `libreoffice` on `$PATH` → the macOS app bundle** (`/Applications/LibreOffice.app/Contents/MacOS/soffice`). `NULL` input yields `NULL` output.

## Recipes

### Folder census, one line

```sql
SELECT file, page_count, is_encrypted, file_size FROM pdf_info('docs/*.pdf');
```

### RAG over a folder of PDFs in three statements

The extension stays a deterministic reader/writer — **it ships no ML, does not embed, and does not call models**. But `pdf_chunks` plus DuckDB's core [`vss`](https://duckdb.org/docs/stable/core_extensions/vss) extension is a complete local RAG substrate; you bring the embedder. The full runnable recipe lives in [`examples/rag_pdf_vss.sql`](examples/rag_pdf_vss.sql).

```sql
-- 1. chunk every PDF in the folder (deterministic, section-aware)
CREATE TABLE rag_chunks AS FROM pdf_chunks('docs/*.pdf');

-- 2. add an embedding column and populate it with YOUR embedder of choice
--    (this is the shape, not the call — fill it from your embedding pipeline)
ALTER TABLE rag_chunks ADD COLUMN embedding FLOAT[384];
-- UPDATE rag_chunks SET embedding = <your_embedder>(text);

-- 3. index and search with the core vss extension
INSTALL vss; LOAD vss;
SET hnsw_enable_experimental_persistence = true;
CREATE INDEX chunks_hnsw ON rag_chunks USING HNSW (embedding);

SELECT file, heading, page_start, text
FROM rag_chunks
ORDER BY array_distance(embedding, [/* query embedding */]::FLOAT[384])
LIMIT 5;
```

Every hit comes back with its `file`, `page_start`/`page_end`, and the `heading` of the section it came from — citation-ready without a second lookup.

### Every form field from a folder of scanned government forms

Digital forms answer directly from the AcroForm dictionary; scanned (flattened) forms fall back to OCR word geometry:

```sql
-- digital forms: the fields are structured data
SELECT file, field_name, field_type, value
FROM pdf_form_fields('forms/*.pdf');

-- scanned forms: recover label/value words with positions via OCR
SELECT filename, page, word, x0, y0, confidence
FROM read_pdf_words('forms/*.pdf', ocr := true)
WHERE source = 'ocr'
ORDER BY filename, page, y1 DESC, x0;
```

### Search, extract, and shrink to email size

Find the pages that matter across a folder, pull just those pages into a new document, and compress it:

```sql
-- 1. find which page of which file mentions the contract
CREATE TABLE hits AS
SELECT filename, page
FROM read_pdf('docs/*.pdf')
WHERE text ILIKE '%revenue%';

-- 2. extract exactly those pages from the first matching file
SELECT pdf_pages(filename, 'excerpt.pdf', string_agg(page, ',' ORDER BY page))
FROM hits
GROUP BY filename
LIMIT 1;

-- 3. shrink it for email
SELECT pdf_compress('excerpt.pdf', 'excerpt_small.pdf');
```

## OCR support

Pages with no extractable text layer are OCR'd automatically (`auto_ocr`, on by default). Pass `ocr := true` to force OCR on every page, even ones that already have text. OCR applies to `read_pdf`, `read_pdf_lines`, `read_pdf_words`, and `read_pdf_tables`.

**English (eng) is bundled** into the community binary (tessdata_fast, extracted
to a temp dir on first OCR). For the default language you do **not** need
`brew install tesseract-lang` / apt eng models / `TESSDATA_PREFIX`.

Other languages still need a model on disk. Install the usual way and the
extension auto-detects standard package-manager paths:

```bash
# Optional — only for non-English languages (or to override the bundled eng)
# macOS
brew install tesseract-lang

# Debian / Ubuntu
sudo apt-get install tesseract-ocr-deu   # German, etc.

# Windows — UB Mannheim installer puts models under
#   C:\Program Files\Tesseract-OCR\tessdata (auto-detected)
```

**Connecting an existing / non-standard install.** Point at it per query:

```sql
SELECT page, text
FROM read_pdf('scan.pdf', ocr := true, tessdata_dir := '/opt/models/tessdata');
```

Resolution order: **`tessdata_dir` → `TESSDATA_PREFIX` → standard install paths → bundled eng.**
An *explicit* `tessdata_dir` is sticky (a broken path does **not** fall through
to the bundled model). If no model is found, *explicit* OCR (`ocr := true`)
raises a clear error; *implicit* `auto_ocr` is best-effort (empty text for that
page) so blank pages never fail the whole read.

Select the language with the `ocr_language` parameter (default `eng`), and tune the OCR path with `ocr_dpi` (default 300), `ocr_psm`, and `ocr_oem`:

```sql
SELECT page, text
FROM read_pdf('scan.pdf', ocr := true, ocr_language := 'eng', ocr_dpi := 300);
```

### Preprocessing and confidence retry

Real-world scans are rarely clean — pages come in skewed, grayscale-noisy, or lightly speckled. Before handing a rendered page to Tesseract, the extension runs a conservative [Leptonica](http://www.leptonica.org/) preprocessing pipeline (on by default via `ocr_preprocess := true`):

1. **Grayscale** — convert the rendered page to 8-bit gray (`pixConvertRGBToGray`) if it isn't already.
2. **Deskew** — measure the page's skew angle (`pixFindSkewSweepAndSearch`); if it exceeds 0.3°, rotate the page back with area-map interpolation and a white background so text baselines are level.
3. **Binarize** — apply an Otsu adaptive threshold (`pixOtsuAdaptiveThreshold`) to separate ink from paper.
4. **Despeckle** — at ≥ 300 DPI renders only (where strokes are several pixels wide and 1-pixel specks are safe to drop), a light 2×2 close-open morphological pass removes isolated noise.

Every step is null-guarded: if any Leptonica call fails — or the image is too small to preprocess safely — the pipeline falls back to the unprocessed image rather than erroring, so preprocessing never turns a readable page into a failure. Set `ocr_preprocess := false` to feed Tesseract the raw render.

The extension also retries low-confidence pages at higher resolution (on by default via `ocr_retry := true`): if a page's mean OCR confidence comes back below 55 **and** it was rendered below 400 DPI, the page is re-rendered at 2× DPI, re-preprocessed, and re-OCR'd; whichever pass scored higher wins. This is silent, self-tuning quality — retried pages report the text (and, for `read_pdf_words`, the per-word confidences) of the kept pass. Set `ocr_retry := false` to disable the second pass.

```sql
-- deskew + binarize a batch of crooked scans, with the low-confidence retry on
SELECT filename, page, text
FROM read_pdf('scans/*.pdf', ocr := true, ocr_preprocess := true, ocr_retry := true);
```

### Expert OCR knobs (`ocr_vars` / `ocr_config` / `ocr_image`) + shellfs

Prefer **more parameters on existing functions**, not a pile of new SQL surfaces.
In-process Tess knobs that experts already know from the CLI:

```sql
-- Whitelist digits only (invoice total lines, etc.)
SELECT page, text, ocr_confidence
FROM read_pdf('scan.pdf', ocr := true,
  ocr_vars := MAP {'tessedit_char_whitelist': '0123456789.$'});

-- Config file (tessdata/configs name or absolute path), then -c-style overrides
SELECT page, text
FROM read_pdf('scan.pdf', ocr := true,
  ocr_config := 'digits',
  ocr_vars := MAP {'classify_bln_numeric_mode': '1'});
```

**`ocr_image(blob, …)`** — table function with **named** params (scalars cannot take
named maps). Returns one row: `text`, `confidence` (MeanTextConf 0–100), `format`.
`format := 'text' | 'hocr' | 'tsv'` maps to Tess `GetUTF8Text` / `GetHOCRText` /
`GetTSVText` in-process:

```sql
-- TVF first arg must be foldable (no subqueries) — SET VARIABLE is the usual path.
SET VARIABLE page_png = (
  SELECT poppler_render_page(content, 1, 200) FROM read_blob('scan.pdf')
);
SELECT text, confidence, format
FROM ocr_image(
  getvariable('page_png'),
  language := 'eng',
  psm := 6,
  vars := MAP {'tessedit_char_whitelist': '0123456789'},
  format := 'hocr'   -- or 'text' (default), 'tsv'
);
```

Low-level scalar **`tesseract_ocr`** remains for one-line text (positional only):

```sql
-- blob | lang | psm | oem | tessdata_dir | preprocess | vars | config
SELECT tesseract_ocr(
  poppler_render_page(content, 1, 200),
  'eng', 6, 3, '', true,
  MAP {'tessedit_char_whitelist': '0123456789'},
  ''
)
FROM read_blob('scan.pdf');
```

**Shellfs escape hatch** (full Tesseract CLI / `TESSDATA_PREFIX` before in-process
parity, or any flag the extension has not lifted yet). Requires a DuckDB build
with [shellfs](https://github.com/query-farm/shellfs) (or equivalent `cmd |`
reader). Shape is one SQL pipe — no sidecar script:

```sql
-- Full CLI: env + tessdata + config + stdout as rows
SELECT *
FROM read_csv(
  'TESSDATA_PREFIX=/opt/models tesseract /tmp/page.png stdout -l eng --psm 6 -c tessedit_char_whitelist=0123456789 |',
  header := false, columns := {'text': 'VARCHAR'}
);

-- One-shot text blob
SELECT * FROM read_text(
  'tesseract /tmp/page.png stdout -l eng --tessdata-dir /opt/models/tessdata |'
);
```

Render in-process (or export a PNG path yourself), then OCR out-of-process when
you need a flag we have not wired:

```sql
-- Assume /tmp/page.png already exists (e.g. written from poppler_render_page BLOB)
SELECT * FROM read_text(
  'TESSDATA_PREFIX=/usr/share/tesseract-ocr/5 tesseract /tmp/page.png stdout -l eng |'
);
```

## Scope

This extension targets the everyday PDF operations — reading at every grain, inspection, and document surgery — deterministically, using Poppler, Tesseract, qpdf, and libharu. It deliberately does **not** attempt ML-based layout analysis or table-structure recognition: table extraction combines ruled-line (lattice) detection with a precision-first geometric heuristic, so merged cells and borderless/sparse tables are out of scope. For state-of-the-art document understanding (complex tables, reading-order models), reach for purpose-built tools such as [docling](https://github.com/docling-project/docling), [marker](https://github.com/VikParuchuri/marker), or a cloud Document AI service. A Poppler/Tesseract expert can extend this extension's heuristics (e.g. Leptonica preprocessing) from the same building blocks it already exposes.

## Building from source

### Dependencies

```bash
# macOS
brew install poppler tesseract leptonica libharu qpdf openssl

# Ubuntu / Debian
sudo apt-get install libpoppler-dev libtesseract-dev libleptonica-dev libhpdf-dev libqpdf-dev libssl-dev
```

### Build

```bash
git clone --recurse-submodules https://github.com/asubbarao/duckdb-pdf.git
cd duckdb-pdf
make release
```

On Windows, dependencies are resolved through vcpkg (MSVC toolchain); see `.github/workflows/MainDistributionPipeline.yml` for the exact build configuration.

The compiled extension will be at `build/release/extension/pdf/pdf.duckdb_extension`:

```sql
LOAD 'build/release/extension/pdf/pdf.duckdb_extension';
```

## Platform support

| Platform | Supported |
|---|---|
| Linux (x86_64, arm64) | Yes |
| macOS (x86_64, arm64) | Yes |
| Windows x64 (MSVC) | Yes |
| Windows (mingw, rtools, arm64) | No — see note |
| WebAssembly (all) | No — see note |

All dependencies (Poppler, Tesseract, Leptonica, qpdf, libharu, and their transitive native libraries) are resolved through **vcpkg** and statically linked, so Linux, macOS, and Windows x64 (MSVC) are all first-class. The **mingw/rtools** Windows variants use a different toolchain than the MSVC static linkage this extension relies on, **windows_arm64** is untested, and **WebAssembly** cannot link Poppler/Tesseract — those targets are excluded. On Windows the parallel multi-file `read_pdf` scan runs serially (Poppler is not thread-safe across documents there); results are identical, only the parallelism differs.

## Function reference

| Function | Type | Description |
|---|---|---|
| `read_pdf(files)` | Table | One row per page: text, dimensions, `has_text_layer`, `used_ocr`, **`ocr_confidence`** (NULL if no OCR). Parallel multi-file scan; `ignore_errors` skips bad files. Named OCR knobs include `ocr_vars` MAP + `ocr_config`. |
| `read_pdf_lines(files)` | Table | One row per layout-preserving line. |
| `read_pdf_words(files)` / `read_pdf_layout` | Table | One row per word: bbox, font, OCR source/confidence, geometric `line`, `page_width`/`page_height`. |
| `read_pdf_tables(files)` | Table | One row per detected table row; cells as `VARCHAR[]`. |
| `read_pdf_elements(files)` | Table | One row per layout element (`heading`/`paragraph`/`list_item`/`other`) with bbox. |
| `pdf_chunks(files)` | Table | Retrieval-ready chunks with section headings; `chunk_size`/`overlap` knobs. |
| `pdf_info(files)` | Table | Full per-file census: metadata, timestamps, dimensions, size, encryption. |
| `pdf_pages_info(files)` | Table | One row per page: crop/media size, rotation, orientation, label, duration. |
| `read_pdf_meta(files)` | Table | Legacy per-file metadata (subset of `pdf_info`). |
| `pdf_outline(files)` | Table | One row per bookmark, depth-first. |
| `pdf_attachments(files)` | Table | One row per embedded file, bytes as `BLOB`. |
| `pdf_form_fields(files)` | Table | One row per AcroForm field with type and value. |
| `pdf_annotations(files)` | Table | One row per annotation; `WHERE subtype = 'Link'` extracts hyperlinks. |
| `pdf_revisions(file)` | Table | One row per incremental-update revision, oldest first. |
| `pdf_signatures(files)` | Table | One row per digital signature: metadata + OpenSSL CMS verification. |
| `pdf_images(files)` | Table | One row per embedded image XObject; `data` is JPEG/JP2/PNG/raw bytes per `format`. |
| `pdf_page_images(files)` | Table | One row per page with a rendered PNG (`png` BLOB); `dpi` / page-range / `password`. No `pdftoppm`. |
| `pdf_write_page_images(files, out_dir)` | Table | Write page PNGs to `out_dir/<stem>/p{N}.png` (1-based, no pad); `(file, page, out_path, width, height, bytes)`. |
| `pdf_split(file, dir)` | Table | One single-page PDF per page; one row per emitted file. |
| `pdf_split_blank(file, dir[, blank_threshold])` | Table | Splits on blank-page separators (mailroom batches); one row per emitted document. |
| `pdf_redact(in, out, boxes [, dpi, password])` | Table | True raster redaction: replace boxed pages with image-only pages (text removed, not covered); one row per output page. |
| `pdf_redact_lateral(in, out, boxes)` | Table (in-out) | Column-ref / dependent-join form of `pdf_redact` (positional only; dpi=200, password=''). |
| `pdf_to_text(src [, layout])` | Scalar | Whole document as plain text. Path or `BLOB`. |
| `pdf_to_markdown(path)` | Scalar | Whole document as GitHub-flavoured Markdown. |
| `pdf_to_html(src)` | Scalar | Whole document as positioned HTML. Path or `BLOB`. |
| `pdf_to_xml(src)` | Scalar | Whole document as per-word-bbox XML. Path or `BLOB`. |
| `pdf_to_svg(src, page [, dpi])` | Scalar | One page as SVG (embedded PNG raster). Path or `BLOB`. |
| `pdf_to_png(src, page [, dpi])` | Scalar | One page as PNG bytes (`BLOB`). Path or `BLOB`. |
| `poppler_version()` | Scalar | Linked Poppler version string. |
| `poppler_render_page(blob, page, dpi)` | Scalar | One page of a PDF BLOB as PNG bytes (building block for OCR). |
| `tesseract_ocr(blob[, …])` | Scalar | In-process OCR text from image BLOB (positional overloads: lang, psm, oem, tessdata_dir, preprocess, vars MAP, config). |
| `ocr_image(blob, …)` | Table | Named-param image OCR: one row `(text, confidence, format)`. `format` = `text`\|`hocr`\|`tsv`; same knobs as readers (`language`, `psm`, `vars`, …). TVF arg must be foldable (use `SET VARIABLE` / `getvariable`). |
| `pdf_merge(files[], out)` | Scalar | Concatenate PDFs in list order. |
| `pdf_rotate(in, out, deg [, pages])` | Scalar | Rotate pages by multiples of 90°. |
| `pdf_pages(in, out, range)` | Scalar | Extract a page subset (`'1-3,7'`, `'z'`, `'r2'`). |
| `pdf_watermark(in, out, text [, opacity])` | Scalar | Stamp a diagonal gray text watermark on every page (real text; default opacity 0.30). |
| `pdf_bates(in, out, prefix, start)` | Scalar | Bates numbering: `prefix` + zero-padded sequential number, bottom-right of each page. |
| `pdf_compress(in, out)` | Scalar | Structural compression + linearization. |
| `pdf_encrypt(in, out, userpw [, ownerpw])` | Scalar | AES-256 password protection. |
| `pdf_decrypt(in, out, pw)` | Scalar | Remove password protection. |
| `pdf_sign(in, out, cert, key)` | Table | Apply an `adbe.pkcs7.detached` CMS signature (PEM cert+key); verifies via `pdf_signatures`. |
| `write_pdf(text [, out])` | Scalar | Native text-to-PDF via libharu. |
| `to_pdf(in [, out])` | Scalar | Office/markup document to PDF via LibreOffice (runtime shell-out). |
| `COPY ... (FORMAT pdf)` | Copy | Query result to a typeset PDF; `TITLE`/`AUTHOR`/`HEADER`/`FOOTER`/`FONT_SIZE`/`PAGE_SIZE`/`MARGIN`. |

## License

This extension is licensed under the **GNU General Public License v2.0 or later (GPL-2.0-or-later)**.

It statically links [Poppler](https://poppler.freedesktop.org/), which is distributed under the GPL-2.0 license. Any binary distribution of this extension is therefore subject to the GPL-2.0 terms: you must make the corresponding source code available to recipients.

See the [LICENSE](LICENSE) file for the full license text.

duckdb-pdf

File Explorer

# Use via CDN

Was this content helpful?

Recent Feedback

Download README