DuckDB Documentation

DuckDB version 0.9.2

Generated on 2023‑11‑13 at 11:48 UTC

Contents

Contents i

Summary 1

Documentation 3

Connect 5

Data Import 7

Importing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

CSV Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

CSV Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

CSV Auto Detection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

CSV Import Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

JSON Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

JSON Loading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Multiple Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Reading Multiple Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

Combining Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

Parquet Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Reading and Writing Parquet Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31

Querying Parquet Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

Parquet Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37

Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Hive Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38

Partitioned Writes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

Appender . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Insert Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42

Client APIs 45

Client APIs Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

DuckDB Documentation

C . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

C API ‑ Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

C API ‑ Startup & Shutdown . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46

C API ‑ Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

C API ‑ Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

C API ‑ Data Chunks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

C API ‑ Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

C API ‑ Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

C API ‑ Prepared Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109

C API ‑ Appender . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125

C API ‑ Table Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135

C API ‑ Replacement Scans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152

C API ‑ Complete API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155

C++ API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273

CLI API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279

Java JDBC API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293

Julia Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297

Node.js . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298

Node.js API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298

NodeJS API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301

Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320

Python API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320

Data Ingestion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

Result Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328

Python DB API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330

Relational API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333

Python Function API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340

Types API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344

Expression API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348

Spark API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352

Python Client API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353

Known Python Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353

R API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354

Rust API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358

Scala JDBC API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359

Swi API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361

Wasm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361

DuckDB Wasm . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361

DuckDB Documentation

Instantiation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362

Data Ingestion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364

Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369

ADBC API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372

ODBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380

ODBC API ‑ Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380

ODBC API ‑ Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

ODBC API ‑ Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384

ODBC API ‑ MacOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387

SQL 391

SQL Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

Statements Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

Alter Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401

Alter View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404

Attach/Detach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405

Call . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408

Checkpoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408

Copy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409

Create Macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415

Create Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417

Create Sequence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417

Create Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420

Create View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 424

Create Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425

Delete Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426

Drop Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426

Export & Import Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427

Insert Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428

Pivot Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431

Select Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440

Set/Reset . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443

Unpivot Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444

Update Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452

Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454

Vacuum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455

iii

DuckDB Documentation

Query Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455

SELECT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455

FROM & JOIN Clauses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458

WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464

GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464

GROUPING SETS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 466

HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468

ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469

LIMIT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471

SAMPLE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472

UNNEST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473

WITH Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474

WINDOW Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481

QUALIFY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482

VALUES Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 484

FILTER Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 484

Set Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488

Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490

Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490

Bitstring Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493

Blob Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493

Boolean Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494

Date Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495

Enum Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497

Interval Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500

List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502

Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504

NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506

Numeric Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507

Struct . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510

Text Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514

Time Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 516

Timestamp Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517

Time Zones . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 520

Union . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544

Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

Case Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547

DuckDB Documentation

Casting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548

Collations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549

Comparisons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552

IN Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553

Logical Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554

Star Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554

Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557

Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561

Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561

Bitstring Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561

Blob Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564

Date Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564

Date Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 567

Date Parts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571

Enum Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575

Interval Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576

Nested Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578

Numeric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596

Pattern Matching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600

Text Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607

Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 620

Timestamp Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 622

Timestamp with Time Zone Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 629

Utility Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639

Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 642

Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649

Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 656

Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 658

Information Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 661

DuckDB_% Metadata Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 665

Pragmas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 680

Rules for Case Sensitivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 686

Samples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687

Window Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 690

Extensions 699

Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699

Oicial Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 701

DuckDB Documentation

Working with Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 703

Arrow Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 704

AutoComplete Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 704

AWS Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 706

Azure Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 708

Excel Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 708

Full Text Search Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709

httpfs Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 713

Iceberg Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 718

ICU Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 720

inet Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 720

jemalloc Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721

JSON Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 721

MySQL Scanner Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 738

PostgreSQL Scanner Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 742

Spatial Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 743

SQLite Scanner Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 757

Substrait Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 761

TPC‑DS Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 764

TPC‑H Extension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765

Guides 767

Data Import & Export 769

CSV Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769

CSV Export . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769

Parquet Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 770

Parquet Export . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 770

Parquet Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 770

HTTP Parquet Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 771

S3, GCS, or R2 Parquet Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 771

S3 Parquet Export . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 772

JSON Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773

JSON Export . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 773

Excel Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 774

Excel Export . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775

SQLite Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775

DuckDB Documentation

PostgreSQL Import . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 776

Meta Queries 777

List Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 777

Describe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 778

Summarize . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779

Explain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 780

Profile Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 782

ODBC 785

ODBC 101: A Duck Themed Guide to ODBC . . . . . . . . . . . . . . . . . . . . . . . . . . . 785

Python 795

Install the Python Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 795

Execute SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 795

Jupyter Notebooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 796

SQL on Pandas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 801

Import from Pandas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 802

Export to Pandas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 802

SQL on Apache Arrow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 802

Import from Apache Arrow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805

Export to Apache Arrow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 806

Relational API and Pandas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 807

Multiple Python Threads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 808

DuckDB with Ibis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 811

DuckDB with Polars . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 826

DuckDB with Vaex . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 827

DuckDB with DataFusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 829

Filesystems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 831

SQL Features 833

DuckDB ASOF Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 833

DuckDB Full Text Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 835

SQL Editors 839

DBeaver SQL IDE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 839

Data Viewers 841

Tableau ‑ A Data Visualisation Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 841

CLI Charting ‑ Using DuckDB with CLI Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . 846

vii

DuckDB Documentation

Under the Hood 851

Internals 853

Overview of DuckDB Internals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 853

Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 855

Execution Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 857

Developer Guides 861

Building DuckDB from Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 861

Profiling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 866

Testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 870

SQLLogicTest . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 871

SQLLogicTest ‑ Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 873

SQLLogicTest ‑ Result Verification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 875

SQLLogicTest ‑ Persistent Testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 879

SQLLogicTest ‑ Loops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 880

SQLLogicTest ‑ Multiple Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . 882

Catch C/C++ Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 883

Acknowledgments 885

viii

Summary

This document contains DuckDB's oicial documentation and guides in a single‑file easy‑to‑search

form. If you find any issues, please report them as a GitHub issue. Contributions are very welcome

in the form of pull requests. If you are considering submitting a contribution to the documentation,

please consult our contributor guide.

Code repositories:

• DuckDB source code: github.com/duckdb/duckdb

• DuckDB documentation source code: github.com/duckdb/duckdb‑web

DuckDB Documentation

Documentation

Connect

Connect or Create a Database

To use DuckDB, you must first create a connection to a database. The exact process varies by client.

Most clients take a parameter pointing to a database file to read and write from (the file extension

may be anything, e.g., .db, .duckdb, etc.). If the database file does not exist, it will be created. The

special value :memory: can be used to create an in‑memory database where no data is persisted to

disk (i.e., all data is lost when you exit the process).

See the API docs for client‑specific details.

Data Import

Importing Data

The first step to using a database system is to insert data into that system. DuckDB provides several

data ingestion methods that allow you to easily and eiciently fill up the database. In this section, we

provide an overview of these methods so you can select which one is correct for you.

Insert Statements

Insert statements are the standard way of loading data into a database system. They are suitable

for quick prototyping, but should be avoided for bulk loading as they have significant per‑row over‑

head.

INSERT INTO people VALUES (1, 'Mark');

See here for a more detailed description of insert statements.

CSV Loading

Data can be eiciently loaded from CSV files using the read_csv_auto function or the COPY state‑

ment.

SELECT * FROM read_csv_auto('test.csv');

You can also load data from compressed (e.g., compressed with gzip) CSV files, for example:

SELECT * FROM read_csv_auto('test.csv.gz');

See here for a detailed description of CSV loading.

Parquet Loading

Parquet files can be eiciently loaded and queried using the read_parquet function.

DuckDB Documentation

SELECT * FROM read_parquet('test.parquet');

See here for a detailed description of Parquet loading.

JSON Loading

JSON files can be eiciently loaded and queried using the read_json_auto function.

SELECT * FROM read_json_auto('test.json');

See here for a detailed description of JSON loading.

Appender (C++ and Java)

In C++ and Java, the appender can be used as an alternative for bulk data loading. This class can be

used to eiciently add rows to the database system without needing to use SQL.

C++:

Appender appender(con, "people");

appender.AppendRow(1, "Mark");

appender.Close();

Java:

con

.createAppender("main", "people");

appender.beginRow();

appender.append("Mark");

appender.endRow();

appender.close();

See here for a detailed description of the C++ appender.

CSV Files

CSV Import

Examples

-- read a CSV file from disk, auto-infer options

SELECT * FROM 'flights.csv';

-- read_csv with custom options

DuckDB Documentation

SELECT * FROM read_csv('flights.csv', delim='|', header=true,

columns={'FlightDate': 'DATE', 'UniqueCarrier': 'VARCHAR',

'OriginCityName': 'VARCHAR', 'DestCityName': 'VARCHAR'});



-- read a CSV from stdin, auto-infer options

cat data/csv/issue2471.csv | duckdb -c "SELECT * FROM read_csv_

auto('/dev/stdin')"

-- read a CSV file into a table

CREATE TABLE ontime(FlightDate DATE, UniqueCarrier VARCHAR, OriginCityName

VARCHAR, DestCityName VARCHAR);

COPY ontime FROM 'flights.csv' (AUTO_DETECT true);

-- alternatively, create a table without specifying the schema manually

CREATE TABLE ontime AS SELECT * FROM 'flights.csv';

-- we can use the FROM-first syntax to omit 'SELECT *'

CREATE TABLE ontime AS FROM 'flights.csv';

-- write the result of a query to a CSV file

COPY (SELECT * FROM ontime) TO 'flights.csv' WITH (HEADER 1, DELIMITER '|');

-- we can use the FROM-first syntax to omit 'SELECT *'

COPY (FROM ontime) TO 'flights.csv' WITH (HEADER 1, DELIMITER '|');

CSV Loading

CSV loading, i.e., importing CSV files to the database, is a very common, and yet surprisingly tricky,

task. While CSVs seem simple on the surface, there are a lot of inconsistencies found within CSV files

that can make loading them a challenge. CSV files come in many dierent varieties, are oen corrupt,

and do not have a schema. The CSV reader needs to cope with all of these dierent situations.

The DuckDB CSV reader can automatically infer which configuration flags to use by analyzing the CSV

file. This will work correctly in most situations, and should be the first option attempted. In rare sit‑

uations where the CSV reader cannot figure out the correct configuration it is possible to manually

configure the CSV reader to correctly parse the CSV file. See the auto detection page for more infor‑

mation.

Parameters

Below are parameters that can be passed to the CSV reader. These parameters are accepted by both

the COPY statement and the CSV reader functions (read_csv and read_csv_auto).

DuckDB Documentation

Name Description Type Default

all_varchar Option to skip type detection for CSV

parsing and assume all columns to be of

type VARCHAR.

BOOL false

auto_detect Enables auto detection of parameters. BOOL true

buffer_size The buer size used by the CSV reader,

specified in bytes. By default, it is set to

32MB or the size of the CSV file (if smaller).

The buer size must be at least as large as

the longest line in the CSV file. Note: this is

an advanced option that has a significant

impact on performance and memory

usage.

BIGINT min(32000000,

CSV file size)

columns A struct that specifies the column names

and column types contained within the

CSV file (e.g., {'col1': 'INTEGER',

'col2': 'VARCHAR'}). Using this

option implies that auto detection is not

used.

STRUCT (empty)

compression The compression type for the file. By

default this will be detected automatically

from the file extension (e.g., t.csv.gz

will use gzip, t.csv will use none).

Options are none, gzip, zstd.

VARCHAR auto

dateformat Specifies the date format to use when

parsing dates. See Date Format.

VARCHAR (empty)

decimal_

separator

The decimal separator of numbers. VARCHAR .

delim or sep Specifies the string that separates

columns within each row (line) of the file.

VARCHAR ,

escape Specifies the string that should appear

before a data character sequence that

matches the quote value.

VARCHAR "

DuckDB Documentation

Name Description Type Default

filename Whether or not an extra filename

column should be included in the result.

BOOL false

force_not_null Do not match the specified columns'

values against the NULL string. In the

default case where the NULL string is

empty, this means that empty values will

be read as zero‑length strings rather than

NULLs.

VARCHAR[] []

header Specifies that the file contains a header

line with the names of each column in the

file.

BOOL false

hive_

partitioning

Whether or not to interpret the path as a

hive partitioned path.

BOOL false

ignore_errors Option to ignore any parsing errors

encountered ‑ and instead ignore rows

with errors.

BOOL false

max_line_size The maximum line size in bytes. BIGINT 2097152

names The column names as a list, see example. VARCHAR[] (empty)

new_line Set the new line character(s) in the file.

Options are '\r','\n', or '\r\n'.

VARCHAR (empty)

normalize_

names

Boolean value that specifies whether or

not column names should be normalized,

removing any non‑alphanumeric

characters from them.

BOOL false

null_padding If this option is enabled, when a row lacks

columns, it will pad the remaining

columns on the right with null values.

BOOL false

nullstr Specifies the string that represents a NULL

value.

VARCHAR (empty)

parallel Whether or not the parallel CSV reader is

used.

BOOL true

DuckDB Documentation

Name Description Type Default

quote Specifies the quoting string to be used

when a data value is quoted.

VARCHAR "

sample_size The number of sample rows for auto

detection of parameters.

BIGINT 20480

skip The number of lines at the top of the file to

skip.

BIGINT 0

timestampformat Specifies the date format to use when

parsing timestamps. See Date Format

VARCHAR (empty)

types or dtypes The column types as either a list (by

position) or a struct (by name). Example

here.

VARCHAR[]

STRUCT

(empty)

union_by_name Whether the columns of multiple schemas

should be unified by name, rather than by

position.

BOOL false

read_csv_auto Function

The read_csv_auto is the simplest method of loading CSV files: it automatically attempts to fig‑

ure out the correct configuration of the CSV reader. It also automatically deduces types of columns.

If the CSV file has a header, it will use the names found in that header to name the columns. Other‑

wise, the columns will be named column0, column1, column2, .... An example with the

flights.csv file:

SELECT * FROM read_csv_auto('flights.csv');

FlightDate UniqueCarrier OriginCityName DestCityName

1988‑01‑01 AA New York, NY Los Angeles, CA

1988‑01‑02 AA New York, NY Los Angeles, CA

1988‑01‑03 AA New York, NY Los Angeles, CA

The path can either be a relative path (relative to the current working directory) or an absolute path.

We can use read_csv_auto to create a persistent table as well:

DuckDB Documentation

CREATE TABLE ontime AS SELECT * FROM read_csv_auto('flights.csv');

DESCRIBE ontime;

Field Type Null Key Default Extra

FlightDate DATE YES NULL NULL NULL

UniqueCarrier VARCHAR YES NULL NULL NULL

OriginCityName VARCHAR YES NULL NULL NULL

DestCityName VARCHAR YES NULL NULL NULL

SELECT * FROM read_csv_auto('flights.csv', SAMPLE_SIZE=20000);

If we set DELIM/SEP, QUOTE, ESCAPE, or HEADERexplicitly, we can bypass the automatic detection

of this particular parameter:

SELECT * FROM read_csv_auto('flights.csv', HEADER=true);

Multiple files can be read at once by providing a glob or a list of files. Refer to the multiple files section

for more information.

read_csv Function

The read_csv function accepts the same parameters that read_csv_auto does but does not as‑

sume AUTO_DETECT=true.

Writing Using the COPY Statement

The COPY statement can be used to load data from a CSV file into a table. This statement has the

same syntax as the one used in PostgreSQL. To load the data using the COPY statement, we must

first create a table with the correct schema (which matches the order of the columns in the CSV file

and uses types that fit the values in the CSV file). We then specify the CSV file to load from plus any

configuration options separately.

CREATE TABLE ontime(flightdate DATE, uniquecarrier VARCHAR, origincityname

VARCHAR, destcityname VARCHAR);

COPY ontime FROM 'flights.csv' (DELIMITER '|', HEADER);

SELECT * FROM ontime;

DuckDB Documentation

flightdate uniquecarrier origincityname destcityname

1988‑01‑01 AA New York, NY Los Angeles, CA

1988‑01‑02 AA New York, NY Los Angeles, CA

1988‑01‑03 AA New York, NY Los Angeles, CA

If we want to use the automatic format detection, we can set AUTO_DETECT to true and omit the

otherwise required configuration options.

CREATE TABLE ontime(flightdate DATE, uniquecarrier VARCHAR, origincityname

VARCHAR, destcityname VARCHAR);

COPY ontime FROM 'flights.csv' (AUTO_DETECT true);

SELECT * FROM ontime;

CSV Auto Detection

When using read_csv_auto, or reading a CSV file with the auto_detectflag set, the system tries

to automatically infer how to read the CSV file. This step is necessary because CSV files are not self‑

describing and come in many dierent dialects. The auto‑detection works roughly as follows:

• Detect the dialect of the CSV file (delimiter, quoting rule, escape)

• Detect the types of each of the columns

• Detect whether or not the file has a header row

By default the system will try to auto‑detect all options. However, options can be individually overrid‑

den by the user. This can be useful in case the system makes a mistake. For example, if the delimiter

is chosen incorrectly, we can override it by calling the read_csv_auto with an explicit delimiter

(e.g., read_csv_auto('file.csv', delim='|')).

The detection works by operating on a sample of the file. The size of the sample can be modified by

setting the sample_size parameter. The default sample size is 20480 rows. Setting the sample_

size parameter to -1 means the entire file is read for sampling. The way sampling is performed

depends on the type of file. If we are reading from a regular file on disk, we will jump into the file

and try to sample from dierent locations in the file. If we are reading from a file in which we cannot

jump ‑ such as a .gz compressed CSV file or stdin ‑ samples are taken only from the beginning of

the file.

DuckDB Documentation

Dialect Detection

Dialect detection works by attempting to parse the samples using the set of considered values. The

detected dialect is the dialect that has (1) a consistent number of columns for each row, and (2) the

highest number of columns for each row.

The following dialects are considered for automatic dialect detection.

Parameters Considered values

delim , | ; \t

quote " ' (empty)

escape " ' \ (empty)

Consider the example file flights.csv:

FlightDate|UniqueCarrier|OriginCityName|DestCityName

1988-01-01|AA|New York, NY|Los Angeles, CA

1988-01-02|AA|New York, NY|Los Angeles, CA

1988-01-03|AA|New York, NY|Los Angeles, CA

In this file, the dialect detection works as follows:

• If we split by a | every row is split into 4 columns

• If we split by a , rows 2‑4 are split into 3 columns, while the first row is split into 1 column

• If we split by ;, every row is split into 1 column

• If we split by \t, every row is split into 1 column

In this example ‑ the system selects the | as the delimiter. All rows are split into the same amount of

columns, and there is more than one column per row meaning the delimiter was actually found in the

CSV file.

Type Detection

Aer detecting the dialect, the system will attempt to figure out the types of each of the columns. Note

that this step is only performed if we are calling read_csv_auto. In case of the COPYstatement the

types of the table that we are copying into will be used instead.

The type detection works by attempting to convert the values in each column to the candidate types.

If the conversion is unsuccessful, the candidate type is removed from the set of candidate types for

DuckDB Documentation

that column. Aer all samples have been handled ‑ the remaining candidate type with the highest

priority is chosen. The set of considered candidate types in order of priority is given below:

Types

BOOLEAN

BIGINT

DOUBLE

TIME

DATE

TIMESTAMP

VARCHAR

Note everything can becast to VARCHAR. This type has the lowest priority ‑ i.e., columnsare converted

to VARCHAR if they cannot be cast to anything else. In flights.csvthe FlightDate column will

be cast to a DATE, while the other columns will be cast to VARCHAR.

The detected types can be individually overridden using the types option. This option takes either a

list of types (e.g., types=[INT, VARCHAR, DATE]) which overrides the types of the columns in‑

order of occurrencein the CSV file. Alternatively, typestakes a name -> typemap which overrides

options of individual columns (e.g., types={'quarter': INT}).

The type detection can be entirely disabled by using the all_varchar option. If this is set all

columns will remain as VARCHAR (as they originally occur in the CSV file).

Header Detection

Header detection works by checking if the candidate header row deviates from the other rows in the

file in terms of types. For example, in flights.csv, we can see that the header row consists of only

VARCHAR columns ‑ whereas the values contain a DATE value for the FlightDate column. As such

‑ the system defines the first row as the header row and extracts the column names from the header

row.

In files that do not have a header row, the column names are generated as column0, column1, etc.

Note that headers cannot be detected correctly if all columns are of type VARCHAR ‑ as in this case

the system cannot distinguish the header row from the other rows in the file. In this case the system

assumes the file has no header. This can be overridden using the header option.

DuckDB Documentation

Dates and Timestamps

DuckDB supports the ISO 8601 format format by default for timestamps, dates and times. Unfortu‑

nately, not all dates and times are formatted using this standard. For that reason, the CSV reader also

supports the dateformat and timestampformat options. Using this format the user can specify

a format string that specifies how the date or timestamp should be read.

As part of the auto‑detection, the system tries to figure out if dates and times are stored in a dier‑

ent representation. This is not always possible ‑ as there are ambiguities in the representation. For

example, the date 01-02-2000 can be parsed as either January 2nd or February 1st. Oen these

ambiguities can be resolved. For example, if we later encounter the date 21-02-2000then we know

that the format musthavebeenDD-MM-YYYY. MM-DD-YYYYis no longer possible as there is no 21nd

month.

If the ambiguities cannot be resolved by looking at the data the system has a list of preferences for

which date format to use. If the system choses incorrectly, the user can specify the dateformatand

timestampformat options manually.

The system considers the following formats for dates (dateformat). Higher entries are chosen over

lower entries in case of ambiguities (i.e., ISO 8601 is preferred over MM-DD-YYYY).

dateformat

ISO 8601

%y-%m-%d

%Y-%m-%d

%d-%m-%y

%d-%m-%Y

%m-%d-%y

%m-%d-%Y

The system considers the following formats for timestamps (timestampformat). Higher entries

are chosen over lower entries in case of ambiguities.

timestampformat

ISO 8601

DuckDB Documentation

timestampformat

%y-%m-%d %H:%M:%S

%Y-%m-%d %H:%M:%S

%d-%m-%y %H:%M:%S

%d-%m-%Y %H:%M:%S

%m-%d-%y %I:%M:%S %p

%m-%d-%Y %I:%M:%S %p

%Y-%m-%d %H:%M:%S.%f

CSV Import Tips

Below is a collection of tips to help when attempting to import complex CSV files. In the examples, we

use the flights.csv file.

Override the Header Flag if the Header Is Not Correctly Detected If a file contains only string

columns the header auto‑detection might fail. Provide the header option to override this behav‑

ior.

SELECT * FROM read_csv_auto('flights.csv', header=true);

Provide Names if the File Does Not Contain a Header If the file does not contain a header, names

will be auto‑generated by default. You can provide your own names with the names option.

SELECT * FROM read_csv_auto('flights.csv', names=['DateOfFlight',

'CarrierName']);

Override the Types of Specific Columns The types flag can be used to override types of only

certain columns by providing a struct of name -> type mappings.

SELECT * FROM read_csv_auto('flights.csv', types={'FlightDate': 'DATE'});

Use COPY When Loading Data into a Table The COPY statement copies data directly into a table.

The CSV reader uses the schema of the table instead of auto‑detecting types from the file. This speeds

up the auto‑detection, and prevents mistakes from being made during auto‑detection.

COPY tbl FROM 'test.csv' (AUTO_DETECT 1);

DuckDB Documentation

Use union_by_name When Loading Files with Dierent Schemas The union_by_name op‑

tion can be used to unify the schema of files that have dierent or missing columns. For files that do

not have certain columns, NULL values are filled in.

SELECT * FROM read_csv_auto('flights*.csv', union_by_name=true);

JSON Files

JSON Loading

Examples

-- read a JSON file from disk, auto-infer options

SELECT * FROM 'todos.json';

-- read_json with custom options

SELECT *

FROM read_json('todos.json',

format='array',

columns={userId: 'UBIGINT',

id: 'UBIGINT',

title: 'VARCHAR',

completed: 'BOOLEAN'});

-- read a JSON file from stdin, auto-infer options

cat data/json/todos.json | duckdb -c "SELECT * FROM read_json_

auto('/dev/stdin')"

-- read a JSON file into a table

CREATE TABLE todos(userId UBIGINT, id UBIGINT, title VARCHAR, completed

BOOLEAN);

COPY todos FROM 'todos.json';

-- alternatively, create a table without specifying the schema manually

CREATE TABLE todos AS SELECT * FROM 'todos.json';

-- write the result of a query to a JSON file

COPY (SELECT * FROM todos) TO 'todos.json';

JSON Loading

JSON is an open standard file format and data interchange format that uses human‑readable text to

store and transmit data objects consisting of attribute–value pairs and arrays (or other serializable

DuckDB Documentation

values). While it is not a very eicient format for tabular data, it is very commonly used, especially as

a data interchange format.

The DuckDB JSON reader can automatically infer which configuration flags to use by analyzing the

JSON file. This will work correctly in most situations, and should be the first option attempted. In

rare situations where the JSON reader cannot figure out the correct configuration, it is possible to

manually configure the JSON reader to correctly parse the JSON file.

Below are parameters that can be passed in to the JSON reader.

Parameters

Name Description Type Default

maximum_

object_size

The maximum size of a JSON object (in

bytes)

UINTEGER 16777216

format Can be one of ['auto',

'unstructured', 'newline_

delimited', 'array']

VARCHAR 'array'

ignore_errors Whether to ignore parse errors (only

possible when format is 'newline_

delimited')

BOOL false

compression The compression type for the file. By

default this will be detected automatically

from the file extension (e.g., t.json.gz

will use gzip, t.json will use none).

Options are 'none', 'gzip', 'zstd',

and 'auto'.

VARCHAR 'auto'

columns A struct that specifies the key names and

value types contained within the JSON file

(e.g., {key1: 'INTEGER', key2:

'VARCHAR'}). If auto_detect is

enabled these will be inferred

STRUCT (empty)

records Can be one of ['auto', 'true',

'false']

VARCHAR 'records'

DuckDB Documentation

Name Description Type Default

auto_detect Whether to auto‑detect detect the names

of the keys and data types of the values

automatically

BOOL false

sample_size Option to define number of sample

objects for automatic JSON type detection.

Set to ‑1 to scan the entire input file

UBIGINT 20480

maximum_depth Maximum nesting depth to which the

automatic schema detection detects types.

Set to ‑1 to fully detect nested JSON types

BIGINT -1

dateformat Specifies the date format to use when

parsing dates. See Date Format

VARCHAR 'iso'

timestampformat Specifies the date format to use when

parsing timestamps. See Date Format

VARCHAR 'iso'

filename Whether or not an extra filename

column should be included in the result.

BOOL false

hive_

partitioning

Whether or not to interpret the path as a

hive partitioned path.

BOOL false

union_by_name Whether the schema's of multiple JSON

files should be unified.

BOOL false

When using read_json_auto, every parameter that supports auto‑detection is enabled.

Examples of Format Settings

The JSON extension can attempt to determine the format of a JSON file when setting format to

auto.

Here are some example JSON files and the corresponding format settings that should be used.

In each of the below cases, the format setting was not needed, as DuckDB was able to infer it cor‑

rectly, but it is included for illustrative purposes. A query of this shape would work in each case:

SELECT * FROM filename.json;

DuckDB Documentation

Format: newline_delimited With format='newline_delimited' newline‑delimited JSON

can be parsed. Each line is a JSON.

{"key1":"value1", "key2": "value1"}

{"key1":"value2", "key2": "value2"}

{"key1":"value3", "key2": "value3"}

SELECT * FROM read_json_auto('records.json', format='newline_delimited');

key1 key2

value1 value1

value2 value2

value3 value3

Format: array If the JSON file contains a JSON array of objects (pretty‑printed or not), array_of_

objects may be used.

[

{"key1":"value1", "key2": "value1"},

{"key1":"value2", "key2": "value2"},

{"key1":"value3", "key2": "value3"}

]

SELECT * FROM read_json_auto('array.json', format='array');

key1 key2

value1 value1

value2 value2

value3 value3

Format: unstructured If the JSON file contains JSON that is not newline‑delimited or an array, un-

structured may be used.

{

"key1":"value1",

"key2": "value1"

}

DuckDB Documentation

{

"key1":"value2",

"key2": "value2"

}

{

"key1":"value3",

"key2": "value3"

}

SELECT * FROM read_json_auto('unstructured.json', format='unstructured');

key1 key2

value1 value1

value2 value2

value3 value3

Examples of Records Settings

The JSON extension can attempt to determine whether a JSON file contains records when setting

records=auto. Whenrecords=true, theJSONextension expectsJSON objects, and will unpack

the fields of JSON objects into individual columns.

Continuing with the same example file from before:

{"key1":"value1", "key2": "value1"}

{"key1":"value2", "key2": "value2"}

{"key1":"value3", "key2": "value3"}

SELECT * FROM read_json_auto('records.json', records=true);

key1 key2

value1 value1

value2 value2

value3 value3

When records=false, the JSON extension will not unpack the top‑level objects, and create

STRUCTs instead:

DuckDB Documentation

SELECT * FROM read_json_auto('records.json', records=false);

json

{'key1': value1, 'key2': value1}

{'key1': value2, 'key2': value2}

{'key1': value3, 'key2': value3}

This is especially useful if we have non‑object JSON, for example:

[1, 2, 3]

[4, 5, 6]

[7, 8, 9]

SELECT * FROM read_json_auto('arrays.json', records=false);

json

[1, 2, 3]

[4, 5, 6]

[7, 8, 9]

Writing

The contents of tables or the result of queries can be written directly to a JSON file using the COPY

statement. See the COPY documentation for more information.

read_json_auto Function

The read_json_auto is the simplest method of loading JSON files: it automatically attempts

to figure out the correct configuration of the JSON reader. It also automatically deduces types of

columns.

SELECT * FROM read_json_auto('todos.json') LIMIT 5;

DuckDB Documentation

userId id title completed

1 1 delectus aut autem false

1 2 quis ut nam facilis et oicia qui false

1 3 fugiat veniam minus false

1 4 et porro tempora true

1 5 laboriosam mollitia et enim quasi adipisci quia provident illum false

The path can either be a relative path (relative to the current working directory) or an absolute path.

We can use read_json_auto to create a persistent table as well:

CREATE TABLE todos AS SELECT * FROM read_json_auto('todos.json');

DESCRIBE todos;

column_name column_type null key default extra

userId UBIGINT YES

id UBIGINT YES

title VARCHAR YES

completed BOOLEAN YES

If we specify the columns, we can bypass the automatic detection. Note that not all columns need to

be specified:

SELECT *

FROM read_json_auto('todos.json',

columns={userId: 'UBIGINT',

completed: 'BOOLEAN'});

Multiple files can be read at once by providing a glob or a list of files. Refer to the multiple files section

for more information.

COPY Statement

The COPY statement can be used to load data from a JSON file into a table. For the COPY statement,

we must first create a table with the correct schema to load the data into. We then specify the JSON

file to load from plus any configuration options separately.

DuckDB Documentation

CREATE TABLE todos(userId UBIGINT, id UBIGINT, title VARCHAR, completed

BOOLEAN);



COPY todos FROM 'todos.json';

SELECT * FROM todos LIMIT 5;

userId id title completed

1 1 delectus aut autem false

1 2 quis ut nam facilis et oicia qui false

1 3 fugiat veniam minus false

1 4 et porro tempora true

1 5 laboriosam mollitia et enim quasi adipisci quia provident illum false

More on the COPY statement can be found here.

Multiple Files

Reading Multiple Files

DuckDB can read multiple files of dierent types (CSV, Parquet, JSON files) at the same time using

either the glob syntax, or by providing a list of files to read. See the combining schemas page for tips

on reading files with dierent schemas.

CSV

-- read all files with a name ending in ".csv" in the folder "dir"

SELECT * FROM 'dir/*.csv';

-- read all files with a name ending in ".csv", two directories deep

SELECT * FROM '*/*/*.csv';

-- read all files with a name ending in ".csv", at any depth in the folder

"dir"

SELECT * FROM 'dir/**/*.csv';

-- read the CSV files 'flights1.csv' and 'flights2.csv'

SELECT * FROM read_csv_auto(['flights1.csv', 'flights2.csv']);

-- read the CSV files 'flights1.csv' and 'flights2.csv', unifying schemas by

name and outputting a `filename` column

SELECT * FROM read_csv_auto(['flights1.csv', 'flights2.csv'], union_by_

name=true, filename=true);

DuckDB Documentation

Parquet

-- read all files that match the glob pattern

SELECT * FROM 'test/*.parquet';

-- read 3 parquet files and treat them as a single table

SELECT * FROM read_parquet(['file1.parquet', 'file2.parquet',

'file3.parquet']);

-- Read all parquet files from 2 specific folders

SELECT * FROM read_parquet(['folder1/*.parquet', 'folder2/*.parquet']);

-- read all parquet files that match the glob pattern at any depth

SELECT * FROM read_parquet('dir/**/*.parquet');

Multi‑File Reads and Globs

DuckDB can also read a series of Parquet files and treat them as if they were a single table. Note that

this only works if the Parquet files have the same schema. You can specify which Parquet files you

want to read using a list parameter, glob pattern matching syntax, or a combination of both.

List Parameter The read_parquet function can accept a list of filenames as the input parameter.

-- read 3 parquet files and treat them as a single table

SELECT * FROM read_parquet(['file1.parquet', 'file2.parquet',

'file3.parquet']);

Glob Syntax Any file name input to the read_parquet function can either be an exact filename, or

use a glob syntax to read multiple files that match a pattern.

Wildcard Description

* matches any number of any characters (including none)

** matches any number of subdirectories (including none)

? matches any single character

[abc] matches one character given in the bracket

[a-z] matches one character from the range given in the bracket

Note that the ? wildcard in globs is not supported for reads over S3 due to HTTP encoding issues.

Here is an example that reads all the files that end with .parquet located in the test folder:

DuckDB Documentation

-- read all files that match the glob pattern

SELECT * FROM read_parquet('test/*.parquet');

List of Globs The glob syntax and the list input parameter can be combined to scan files that meet

one of multiple patterns.

-- Read all parquet files from 2 specific folders

SELECT * FROM read_parquet(['folder1/*.parquet', 'folder2/*.parquet']);

DuckDB can read multiple CSV files at the same time using either the glob syntax, or by providing a

list of files to read.

Filename

The filename argument can be used to add an extra filename column to the result that indicates

which row came from which file. For example:

SELECT * FROM read_csv_auto(['flights1.csv', 'flights2.csv'], union_by_

name=true, filename=true);

FlightDate OriginCityName DestCityName UniqueCarrier filename

1988‑01‑01 New York, NY Los Angeles, CA NULL flights1.csv

1988‑01‑02 New York, NY Los Angeles, CA NULL flights1.csv

1988‑01‑03 New York, NY Los Angeles, CA AA flights2.csv

Glob Function to Find Filenames

The glob pattern matching syntax can also be used to search for filenames using the glob table func‑

tion. It accepts one parameter: the path to search (which may include glob patterns).

-- Search the current directory for all files

SELECT * FROM glob('*');

file

duckdb.exe

test.csv

DuckDB Documentation

file

test.json

test.parquet

test2.csv

test2.parquet

todos.json

Combining Schemas

Examples

-- read a set of CSV files combining columns by position

SELECT * FROM read_csv_auto('flights*.csv');

-- read a set of CSV files combining columns by name

SELECT * FROM read_csv_auto('flights*.csv', union_by_name=true);

Combining Schemas

When reading from multiple files, we have to combine schemas from those files. That is because

each file has its own schema that can dier from the other files. DuckDB oers two ways of unifying

schemas of multiple files: by column position and by column name.

By default, DuckDB reads the schema of the first file provided, and then unifies columnsin subsequent

files by column position. This works correctly as long as all files have the same schema. If the schema

of the files diers, you might want to use the union_by_name option to allow DuckDB to construct

the schema by reading all of the names instead.

Below is an example of how both methods work.

Union By Position

By default, DuckDB unifies the columns of these dierent files by position. This means that the first

column in each file is combined together, as well as the second column in each file, etc. For example,

consider the following two files.

flights1.csv:

DuckDB Documentation

FlightDate|UniqueCarrier|OriginCityName|DestCityName

1988-01-01|AA|New York, NY|Los Angeles, CA

1988-01-02|AA|New York, NY|Los Angeles, CA

flights2.csv:

FlightDate|UniqueCarrier|OriginCityName|DestCityName

1988-01-03|AA|New York, NY|Los Angeles, CA

Reading the two files at the same time will produce the following result set:

FlightDate UniqueCarrier OriginCityName DestCityName

1988‑01‑01 AA New York, NY Los Angeles, CA

1988‑01‑02 AA New York, NY Los Angeles, CA

1988‑01‑03 AA New York, NY Los Angeles, CA

This is equivalent to the SQL construct UNION ALL.

Union By Name

If you are processing multiple files that have dierent schemas, perhaps because columns have been

added or renamed, it might be desirable to unify the columns of dierent files by name instead. This

can be done by providing the union_by_nameoption. For example, consider the following two files,

where flights4.csv has an extra column (UniqueCarrier).

flights3.csv:

FlightDate|OriginCityName|DestCityName

1988-01-01|New York, NY|Los Angeles, CA

1988-01-02|New York, NY|Los Angeles, CA

flights4.csv:

FlightDate|UniqueCarrier|OriginCityName|DestCityName

1988-01-03|AA|New York, NY|Los Angeles, CA

Reading these when unifying column names by position results in an error ‑ as the two files have a dif‑

ferent number of columns. When specifying the union_by_name option, the columns are correctly

unified, and any missing values are set to NULL.

SELECT * FROM read_csv_auto(['flights3.csv', 'flights4.csv'], union_by_

name=true);

DuckDB Documentation

FlightDate OriginCityName DestCityName UniqueCarrier

1988‑01‑01 New York, NY Los Angeles, CA NULL

1988‑01‑02 New York, NY Los Angeles, CA NULL

1988‑01‑03 New York, NY Los Angeles, CA AA

This is equivalent to the SQL construct UNION ALL BY NAME.

Parquet Files

Reading and Writing Parquet Files

Examples

-- read a single parquet file

SELECT * FROM 'test.parquet';

-- figure out which columns/types are in a parquet file

DESCRIBE SELECT * FROM 'test.parquet';

-- create a table from a parquet file

CREATE TABLE test AS SELECT * FROM 'test.parquet';

-- if the file does not end in ".parquet", use the read_parquet function

SELECT * FROM read_parquet('test.parq');

-- use list parameter to read 3 parquet files and treat them as a single

table

SELECT * FROM read_parquet(['file1.parquet', 'file2.parquet',

'file3.parquet']);

-- read all files that match the glob pattern

SELECT * FROM 'test/*.parquet';

-- read all files that match the glob pattern, and include a "filename"

column that specifies which file each row came from

SELECT * FROM read_parquet('test/*.parquet', filename=true);

-- use a list of globs to read all parquet files from 2 specific folders

SELECT * FROM read_parquet(['folder1/*.parquet', 'folder2/*.parquet']);

-- query the metadata of a parquet file

SELECT * FROM parquet_metadata('test.parquet');

-- query the schema of a parquet file

SELECT * FROM parquet_schema('test.parquet');

-- write the results of a query to a parquet file

DuckDB Documentation

COPY (SELECT * FROM tbl) TO 'result-snappy.parquet' (FORMAT 'parquet');

-- write the results from a query to a parquet file with specific

compression and row_group_size

COPY (FROM generate_series(100000)) TO 'test.parquet' (FORMAT 'parquet',

COMPRESSION 'ZSTD', ROW_GROUP_SIZE 100000);

-- export the table contents of the entire database as parquet

EXPORT DATABASE 'target_directory' (FORMAT PARQUET);

Parquet Files

Parquet files are compressed columnar files that are eicient to load and process. DuckDB provides

support for bothreading and writing Parquetfiles in an eicient manner, as well as support for pushing

filters and projections into the Parquet file scans.

read_parquet Function

Function Description Example

read_parquet(

path(s), *)

Read Parquet file(s) SELECT * FROM read_

parquet('test.parquet');

parquet_scan(

path(s), *)

Alias for read_

parquet

SELECT * FROM parquet_

scan('test.parquet');

If your file ends in .parquet, the function syntax is optional. The system will automatically infer that

you are reading a Parquet file.

SELECT * FROM 'test.parquet';

Multiple files can be read at once by providing a glob or a list of files. Refer to the multiple files section

for more information.

Parameters There are a number of options exposed that can be passed to the read_parquet

function or the COPY statement.

DuckDB Documentation

Name Description Type Default

binary_as_

string

Parquet files generated by legacy writers

do not correctly set the UTF8 flag for

strings, causing string columns to be

loaded as BLOB instead. Set this to true to

load binary columns as strings.

BOOL false

filename Whether or not an extra filename

column should be included in the result.

BOOL false

file_row_

number

Whether or not to include the file_

row_number column.

BOOL false

hive_

partitioning

Whether or not to interpret the path as a

hive partitioned path.

BOOL false

union_by_name

Whether the columns of multiple schemas

should be unified by name, rather than by

position.

BOOL false

Partial Reading

DuckDB supports projection pushdown into the Parquet file itself. That is to say, when querying a

Parquet file, only the columns required for the query are read. This allows you to read only the part of

the Parquet file that you are interested in. This will be done automatically by DuckDB.

DuckDB also supports filter pushdown into the Parquet reader. When you apply a filter to a column

that is scanned from a Parquet file, the filter will be pushed down into the scan, and can even be used

to skip parts of the file using the built‑in zonemaps. Note that this will depend on whether or not your

Parquet file contains zonemaps.

Filter and projection pushdown provide significant performance benefits. See our blog post on this

for more information.

Inserts and Views

You can also insert the data into a table or create a table from the parquet file directly. This will load

the data from the parquet file and insert it into the database.

-- insert the data from the parquet file in the table

INSERT INTO people SELECT * FROM read_parquet('test.parquet');

DuckDB Documentation

-- create a table directly from a parquet file

CREATE TABLE people AS SELECT * FROM read_parquet('test.parquet');

If you wish to keep the data stored inside the parquet file, but want to query the parquet file directly,

you can create a view over the read_parquet function. You can then query the parquet file as if it

were a built‑in table.

-- create a view over the parquet file

CREATE VIEW people AS SELECT * FROM read_parquet('test.parquet');

-- query the parquet file

SELECT * FROM people;

Writing to Parquet Files

DuckDB also has support for writing to Parquet files using the COPY statement syntax. See the COPY

Statement page for details, including all possible parameters for the COPY statement.

-- write a query to a snappy compressed parquet file

COPY (SELECT * FROM tbl) TO 'result-snappy.parquet' (FORMAT 'parquet')

-- write "tbl" to a zstd compressed parquet file

COPY tbl TO 'result-zstd.parquet' (FORMAT 'PARQUET', CODEC 'ZSTD')

-- write a csv file to an uncompressed parquet file

COPY 'test.csv' TO 'result-uncompressed.parquet' (FORMAT 'PARQUET', CODEC

'UNCOMPRESSED')

-- write a query to a parquet file with ZSTD compression (same as CODEC) and

row_group_size

COPY (FROM generate_series(100000)) TO 'row-groups-zstd.parquet' (FORMAT

PARQUET, COMPRESSION ZSTD, ROW_GROUP_SIZE 100000);

DuckDB's EXPORT command can be used to export an entire database to a series of Parquet files. See

the Export statement documentation for more details.

-- export the table contents of the entire database as parquet

EXPORT DATABASE 'target_directory' (FORMAT PARQUET);

Installing and Loading the Parquet Extension

The support for Parquet files is enabled via extension. The parquetextension is bundled with almost

all clients. However, if your client does not bundle the parquet extension, the extension must be

installed and loaded separately.

-- run once

INSTALL parquet;

DuckDB Documentation

-- run before usage

LOAD parquet;

Querying Parquet Metadata

Parquet Metadata

The parquet_metadata function can be used to query the metadata contained within a Parquet

file, which reveals various internal details of the Parquet file such as the statistics of the dierent

columns. This can be useful for figuring out what kind of skipping is possible in Parquet files, or even

to obtain a quick overview of what the dierent columns contain.

SELECT * FROM parquet_metadata('test.parquet');

Below is a table of the columns returned by parquet_metadata.

Field Type

file_name VARCHAR

row_group_id BIGINT

row_group_num_rows BIGINT

row_group_num_columns BIGINT

row_group_bytes BIGINT

column_id BIGINT

file_offset BIGINT

num_values BIGINT

path_in_schema VARCHAR

type VARCHAR

stats_min VARCHAR

stats_max VARCHAR

stats_null_count

BIGINT

stats_distinct_count BIGINT

stats_min_value VARCHAR

stats_max_value VARCHAR

DuckDB Documentation

Field Type

compression VARCHAR

encodings VARCHAR

index_page_offset BIGINT

dictionary_page_offset BIGINT

data_page_offset BIGINT

total_compressed_size BIGINT

total_uncompressed_size BIGINT

Parquet Schema

The parquet_schema function can be used to query the internal schema contained within a Par‑

quet file. Note that this is the schema as it is contained within the metadata of the Parquet file. If

you want to figure out the column names and types contained within a Parquet file it is easier to use

DESCRIBE.

-- fetch the column names and column types

DESCRIBE SELECT * FROM 'test.parquet';

-- fetch the internal schema of a parquet file

SELECT * FROM parquet_schema('test.parquet');

Below is a table of the columns returned by parquet_schema.

Field Type

file_name VARCHAR

name VARCHAR

type VARCHAR

type_length VARCHAR

repetition_type VARCHAR

num_children BIGINT

converted_type VARCHAR

scale BIGINT

DuckDB Documentation

Field Type

precision BIGINT

field_id BIGINT

logical_type VARCHAR

Parquet Tips

Below is a collection of tips to help when dealing with Parquet files.

Tips for reading Parquet files

Use union_by_name when loading files with dierent schemas The union_by_name option

can be used to unify the schema of files that have dierent or missing columns. For files that do not

have certain columns, NULL values are filled in.

SELECT * FROM read_parquet('flights*.parquet', union_by_name=true);

Tips for writing Parquet files

Enabling per_thread_output If the final number of parquet files is not important, writing one

file per thread can significantly improve performance. Using a glob pattern upon read or a hive parti‑

tioning structure are good ways to transparently handle multiple files.

COPY (FROM generate_series(10000000)) TO 'test.parquet' (FORMAT PARQUET,

PER_THREAD_OUTPUT true);

Selecting a row_group_size The ROW_GROUP_SIZE parameter specifies the minimum num‑

ber of rows in a parquet row group, with a minimum value equal to DuckDB's vector size (currently

2048, but adjustable when compiling DuckDB), and a default of 122880. A parquet row group is a

partition of rows, consisting of a column chunk for each column in the dataset.

Compression algorithms are only applied per row group, so the larger the row group size, the more

opportunities to compress the data. DuckDB can read parquet row groups in parallel even within the

same file and uses predicate pushdown to only scan the row groups whose metadata rangesmatch the

WHEREclause of the query. However there is some overhead associated with reading the metadata in

each group. A good approach would be to ensure that within each file, the total number of row groups

DuckDB Documentation

is at least as large as the number of CPU threads used to query that file. More row groups beyond the

thread count would improve the speed of highly selective queries, but slow down queries that must

scan the whole file like aggregations.

-- write a query to a parquet file with a different row_group_size

COPY (FROM generate_series(100000)) TO 'row-groups.parquet' (FORMAT PARQUET,

ROW_GROUP_SIZE 100000);

Partitioning

Hive Partitioning

Examples

-- read data from a hive partitioned data set

SELECT * FROM read_parquet('orders/*/*/*.parquet', hive_partitioning=1);

-- parquet_scan is an alias of read_parquet, so they are equivalent

SELECT * FROM parquet_scan('orders/*/*/*.parquet', hive_partitioning=1);

-- write a table to a hive partitioned data set

COPY orders TO 'orders' (FORMAT PARQUET, PARTITION_BY (year, month));

Hive Partitioning

Hive partitioning is a partitioning strategy that is used to split a table into multiple files based on

partition keys. The files are organized into folders. Within each folder, the partition key has a value

that is determined by the name of the folder.

Below is an example of a hive partitioned file hierarchy. The files are partitioned on two keys (year

and month).

orders

├── year=2021

│ ├── month=1

│ │ ├── file1.parquet

│ │ └── file2.parquet

│ └── month=2

│ └── file3.parquet

└── year=2022

├── month=11

│ ├── file4.parquet

│ └── file5.parquet

DuckDB Documentation

└── month=12

└── file6.parquet

Files stored in this hierarchy can be read using the hive_partitioning flag.

SELECT * FROM read_parquet('orders/*/*/*.parquet', hive_partitioning=1);

When we specify the hive_partitioning flag, the values of the columns will be read from the

directories.

Filter Pushdown Filters on the partition keys are automatically pushed down into the files. This

way the system skips reading files that are not necessary to answer a query. For example, consider

the following query on the above dataset:

SELECT *

FROM read_parquet('orders/*/*/*.parquet', hive_partitioning=1)

WHERE year=2022 AND month=11;

When executing this query, only the following files will be read:

orders

└── year=2022

└── month=11

├── file4.parquet

└── file5.parquet

Autodetection By default the system tries to infer if the provided files are in a hive partitioned hi‑

erarchy. And if so, the hive_partitioning flag is enabled automatically. The autodetection will

look at the names of the folders and search for a 'key'='value' pattern. This behaviour can be overrid‑

den by setting the hive_partitioning flag manually.

Hive Types hive_types is a way to specify the logical types of the hive partitions in a struct:

FROM read_parquet('dir/**/*.parquet', hive_partitioning=1, hive_

types={'release': date, 'orders': bigint});

hive_types will be autodetected for the following types: DATE, TIMESTAMP and BIGINT. To

switch o the autodetection, the flag hive_types_autocast=0 can be set.

Writing Partitioned Files See the Partitioned Writes section.

DuckDB Documentation

Partitioned Writes

Examples

-- write a table to a hive partitioned data set of parquet files

COPY orders TO 'orders' (FORMAT PARQUET, PARTITION_BY (year, month));

-- write a table to a hive partitioned data set of CSV files, allowing

overwrites

COPY orders TO 'orders' (FORMAT CSV, PARTITION_BY (year, month), OVERWRITE_

OR_IGNORE 1);

Partitioned Writes

When the partition_by clause is specified for the COPY statement, the files are written in a hive

partitioned folder hierarchy. The target is the name of the root directory (in the example above: or-

ders). The files are written in‑order in the file hierarchy. Currently, one file is written per thread to

each directory.

orders

├── year=2021

│ ├── month=1

│ │ ├── data_1.parquet

│ │ └── data_2.parquet

│ └── month=2

│ └── data_1.parquet

└── year=2022

├── month=11

│ ├── data_1.parquet

│ └── data_2.parquet

└── month=12

└── data_1.parquet

The values of the partitions are automatically extracted from the data. Note that it can be very expen‑

sive to write many partitions as many files will be created. The ideal partition count depends on how

large your data set is.

Note. Writing data into many small partitions is expensive. It is generally recommended to

have at least 100MB of data per partition.

Overwriting By default the partitioned write will not allow overwriting existing directories. Use the

OVERWRITE_OR_IGNORE option to allow overwriting an existing directory.

DuckDB Documentation

Filename Pattern By default, files will be named data_0.parquet or data_0.csv. With

the flag FILENAME_PATTERN a pattern with {i} or {uuid} can be defined to create specific

filenames:

• {i} will be replaced by an index

• {uuid} will be replaced by a 128 bits long UUID

-- write a table to a hive partitioned data set of .parquet files, with an

index in the filename

COPY orders TO 'orders' (FORMAT PARQUET, PARTITION_BY (year, month),

OVERWRITE_OR_IGNORE, FILENAME_PATTERN "orders_{i}");

-- write a table to a hive partitioned data set of .parquet files, with

unique filenames

COPY orders TO 'orders' (FORMAT PARQUET, PARTITION_BY (year, month),

OVERWRITE_OR_IGNORE, FILENAME_PATTERN "file_{uuid}");

Appender

The C++ Appender can be used to load bulk data into a DuckDB database. The Appender is tied to a

connection, and will use the transaction context of that connection when appending. An Appender

always appends to a single table in the database file.

DuckDB db;

Connection con(db);

// create the table

con.Query("CREATE TABLE people(id INTEGER, name VARCHAR)");

// initialize the appender

Appender appender(con, "people");

The AppendRow function is the easiest way of appending data. It uses recursive templates to allow

you to put all the values of a single row within one function call, as follows:

appender.AppendRow(1, "Mark");

Rows can also be individually constructed using the BeginRow, EndRowand Appendmethods. This

is done internally by AppendRow, and hence has the same performance characteristics.

appender.BeginRow();

appender.Append<int32_t>(2);

appender.Append<string>("Hannes");

appender.EndRow();

Any values added to the appender are cached prior to being inserted into the database system for per‑

formance reasons. That means that, while appending, the rows might not be immediately visible in

DuckDB Documentation

the system. The cache is automatically flushed when the appender goesout of scope or when appen-

der.Close() is called. The cache can also be manually flushed using the appender.Flush()

method. Aer either Flush or Close is called, all the data has been written to the database sys‑

tem.

Date, Time and Timestamps

While numbers and strings are rather self‑explanatory, dates, times and timestamps require some

explanation. They can be directly appended using the methods provided by duckdb::Date,

duckdb::Time or duckdb::Timestamp. They can also be appended using the internal

duckdb::Value type, however, this adds some additional overheads and should be avoided if

possible.

Below is a short example:

con.Query("CREATE TABLE dates(d DATE, t TIME, ts TIMESTAMP)");

Appender appender(con, "dates");

// construct the values using the Date/Time/Timestamp types - this is the

most efficient

appender.AppendRow(Date::FromDate(1992, 1, 1), Time::FromTime(1, 1, 1, 0),

Timestamp::FromDatetime(Date::FromDate(1992, 1, 1), Time::FromTime(1, 1,

1, 0)));



// construct duckdb::Value objects

appender.AppendRow(Value::DATE(1992, 1, 1), Value::TIME(1, 1, 1, 0),

Value::TIMESTAMP(1992, 1, 1, 1, 1, 1, 0));

Insert Statements

Insert statements are the standard way of loading data into a relational database. When using insert

statements, the values are supplied row‑by‑row. While simple, there is significant overhead involved

in parsing and processing individual insert statements. This makes lots of individual row‑by‑row in‑

sertions very ineicient for bulk insertion.

Note. As a rule‑of‑thumb, avoid using lots of individual row‑by‑row insert statements when

inserting more than a few rows (i.e., avoid using insert statements as part of a loop). When bulk

inserting data, try to maximize the amount of data that is inserted per statement.

If you must use insert statements to load data in a loop, avoid executing the statements in auto‑

commit mode. Aer every commit, the database is required to sync the changes made to disk to

DuckDB Documentation

ensure no data is lost. In auto‑commit mode every single statement will be wrapped in a separate

transaction, meaning fsync will be called for every statement. This is typically unnecessary when

bulk loading and will significantly slow down your program.

Note. If you absolutely must use insert statements in a loop to load data, wrap them in calls to

BEGIN TRANSACTION and COMMIT.

Syntax

An example of using INSERT INTO to load data in a table is as follows:

CREATE TABLE people(id INTEGER, name VARCHAR);

INSERT INTO people VALUES (1, 'Mark'), (2, 'Hannes');

A more detailed description together with syntax diagram can be found here.

Client APIs

Client APIs Overview

There are various client APIs for DuckDB. DuckDB's ”native” API is C++, with ”oicial” wrappers avail‑

able for C, Python, R, Java, Node.js, WebAssembly/Wasm, ODBC API, Julia, and a Command Line In‑

terface (CLI).

There are also contributed third‑party DuckDB wrappers for:

• C#, by Giorgi

• Common Lisp, by ak‑coram

• Crystal, by amauryt

• Go, by marcboeker

• Ruby, by suketa

• Rust, by wangfenjin

• Zig, by karlseguin

C API ‑ Overview

DuckDB implements a custom C API modelled somewhat following the SQLite C API. The API is con‑

tained in the duckdb.h header. Continue to Startup & Shutdown to get started, or check out the Full

API overview.

We also provide a SQLite API wrapper which means that if your applications is programmed against

the SQLite C API, you can re‑link to DuckDB and it should continue working. See the sqlite_api_

wrapper folder in our source repository for more information.

DuckDB Documentation

Installation

The DuckDB C API can be installed as part of the libduckdb packages. Please see the installation

page for details.

C API ‑ Startup & Shutdown

To use DuckDB, you must first initialize a duckdb_database handle using duckdb_open().

duckdb_open() takes as parameter the database file to read and write from. The special value

NULL (nullptr) can be used to create an in‑memory database. Note that for an in‑memory

database no data is persisted to disk (i.e., all data is lost when you exit the process).

With the duckdb_database handle, you can create one or many duckdb_connection using

duckdb_connect(). While individual connections are thread‑safe, they will be locked during

querying. It is therefore recommended that each thread uses its own connection to allow for the best

parallel performance.

All duckdb_connections have to explicitly be disconnected with duckdb_disconnect() and

the duckdb_database has to be explicitly closed with duckdb_close() to avoid memory and

file handle leaking.

Example

duckdb_database db;

duckdb_connection con;

if (duckdb_open(NULL, &db) == DuckDBError) {

// handle error

}

if (duckdb_connect(db, &con) == DuckDBError) {

// handle error

}

// run queries...

// cleanup

duckdb_disconnect(&con);

duckdb_close(&db);

DuckDB Documentation

API Reference

duckdb_state duckdb_open(const char *path, duckdb_database *out_database);

duckdb_state duckdb_open_ext(const char *path, duckdb_database *out_

database, duckdb_config config, char **out_error);

void duckdb_close(duckdb_database *database);

duckdb_state duckdb_connect(duckdb_database database, duckdb_connection

*out_connection);

void duckdb_interrupt(duckdb_connection connection);

double duckdb_query_progress(duckdb_connection connection);

void duckdb_disconnect(duckdb_connection *connection);

const char *duckdb_library_version();

duckdb_open Creates a new database or opens an existing database file stored at the given path.

If no path is given a new in‑memory database is created instead. The instantiated database should be

closed with 'duckdb_close'

Syntax

duckdb_state duckdb_open(

const char *path,

duckdb_database *out_database

);

Parameters

• path

Path to the database file on disk, or nullptr or :memory: to open an in‑memory database.

• out_database

The result database object.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_open_ext Extended version of duckdb_open. Creates a new database or opens an exist‑

ing database file stored at the given path.

DuckDB Documentation

Syntax

duckdb_state duckdb_open_ext(

const char *path,

duckdb_database *out_database,

duckdb_config config,

char **out_error

);

Parameters

• path

Path to the database file on disk, or nullptr or :memory: to open an in‑memory database.

• out_database

The result database object.

• config

(Optional) configuration used to start up the database system.

• out_error

If set and the function returns DuckDBError, this will contain the reason why the start‑up failed. Note

that the error must be freed using duckdb_free.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_close Closes the specified database and de‑allocates all memory allocated for that

database. This should be called aer you are done with any database allocated through duckdb_

open. Note that failing to call duckdb_close (in case of e.g., a program crash) will not cause data

corruption. Still it is recommended to always correctly close a database object aer you are done

with it.

Syntax

void duckdb_close(

duckdb_database *database

);

DuckDB Documentation

Parameters

• database

The database object to shut down.

duckdb_connect Opens a connection to a database. Connections are required to query the

database, and store transactional state associated with the connection. The instantiated connection

should be closed using 'duckdb_disconnect'

Syntax

duckdb_state duckdb_connect(

duckdb_database database,

duckdb_connection *out_connection

);

Parameters

• database

The database file to connect to.

• out_connection

The result connection object.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_interrupt Interrupt running query

Syntax

void duckdb_interrupt(

duckdb_connection connection

);

Parameters

• connection

The connection to interruot

DuckDB Documentation

duckdb_query_progress Get progress of the running query

Syntax

double duckdb_query_progress(

duckdb_connection connection

);

Parameters

• connection

The working connection

• returns

‑1 if no progress or a percentage of the progress

duckdb_disconnect Closes the specified connection and de‑allocates all memory allocated for

that connection.

Syntax

void duckdb_disconnect(

duckdb_connection *connection

);

Parameters

• connection

The connection to close.

duckdb_library_version Returns the version of the linked DuckDB, with a version postfix for

dev versions

Usually used for developing C extensions that must return this for a compatibility check.

Syntax

const char *duckdb_library_version(

);

DuckDB Documentation

C API ‑ Configuration

Configuration options can be provided to change dierent settings of the database system. Note that

many of these settings can be changed later on using PRAGMA statements as well. The configuration

object should be created, filled with values and passed to duckdb_open_ext.

Example

duckdb_database db;

duckdb_config config;

// create the configuration object

if (duckdb_create_config(&config) == DuckDBError) {

// handle error

}

// set some configuration options

duckdb_set_config(config, "access_mode", "READ_WRITE"); // or READ_ONLY

duckdb_set_config(config, "threads", "8");

duckdb_set_config(config, "max_memory", "8GB");

duckdb_set_config(config, "default_order", "DESC");

// open the database using the configuration

if (duckdb_open_ext(NULL, &db, config, NULL) == DuckDBError) {

// handle error

}

// cleanup the configuration object

duckdb_destroy_config(&config);

// run queries...

// cleanup

duckdb_close(&db);

API Reference

duckdb_state duckdb_create_config(duckdb_config *out_config);

size_t duckdb_config_count();

duckdb_state duckdb_get_config_flag(size_t index, const char **out_name,

const char **out_description);

duckdb_state duckdb_set_config(duckdb_config config, const char *name, const

char *option);

DuckDB Documentation

void duckdb_destroy_config(duckdb_config *config);

duckdb_create_config Initializes an empty configuration object that can be used to provide

start‑up options for the DuckDB instance through duckdb_open_ext.

This will always succeed unless there is a malloc failure.

Syntax

duckdb_state duckdb_create_config(

duckdb_config *out_config

);

Parameters

• out_config

The result configuration object.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_config_count This returns the total amount of configuration options available for us‑

age with duckdb_get_config_flag.

This should not be called in a loop as it internally loops over all the options.

Syntax

size_t duckdb_config_count(

);

Parameters

• returns

The amount of config options available.

DuckDB Documentation

duckdb_get_config_flag Obtains a human‑readable name and description of a specific con‑

figuration option. This can be used to e.g. display configuration options. This will succeed unless

index is out of range (i.e., >= duckdb_config_count).

The result name or description MUST NOT be freed.

Syntax

duckdb_state duckdb_get_config_flag(

size_t index,

const char **out_name,

const char **out_description

);

Parameters

• index

The index of the configuration option (between 0 and duckdb_config_count)

• out_name

A name of the configuration flag.

• out_description

A description of the configuration flag.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_set_config Sets the specified option for the specified configuration. The configuration

option is indicated by name. To obtain a list of config options, see duckdb_get_config_flag.

In the source code, configuration options are defined in config.cpp.

This can fail if either the name is invalid, or if the value provided for the option is invalid.

Syntax

duckdb_state duckdb_set_config(

duckdb_config config,

const char *name,

const char *option

);

DuckDB Documentation

Parameters

• duckdb_config

The configuration object to set the option on.

• name

The name of the configuration flag to set.

• option

The value to set the configuration flag to.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_destroy_config Destroys the specified configuration option and de‑allocates all

memory allocated for the object.

Syntax

void duckdb_destroy_config(

duckdb_config *config

);

Parameters

• config

The configuration object to destroy.

C API ‑ Query

The duckdb_query method allows SQL queries to be run in DuckDB from C. This method takes two

parameters, a (null‑terminated) SQL query string and a duckdb_result result pointer. The result

pointer may be NULL if the application is not interested in the result set or if the query produces no

result. Aer the result is consumed, the duckdb_destroy_result method should be used to

clean up the result.

Elements can be extracted from the duckdb_result object using a variety of methods. The

duckdb_column_count and duckdb_row_count methods can be used to extract the number

of columns and the number of rows, respectively. duckdb_column_nameand duckdb_column_

type can be used to extract the names and types of individual columns.

DuckDB Documentation

Example

duckdb_state state;

duckdb_result result;

// create a table

state = duckdb_query(con, "CREATE TABLE integers(i INTEGER, j INTEGER);",

NULL);

if (state == DuckDBError) {

// handle error

}

// insert three rows into the table

state = duckdb_query(con, "INSERT INTO integers VALUES (3, 4), (5, 6), (7,

NULL);", NULL);

if (state == DuckDBError) {

// handle error

}

// query rows again

state = duckdb_query(con, "SELECT * FROM integers", &result);

if (state == DuckDBError) {

// handle error

}

// handle the result

// ...

// destroy the result after we are done with it

duckdb_destroy_result(&result);

Value Extraction

Values can be extracted using either the duckdb_column_data/duckdb_nullmask_data

functions, or using the duckdb_value convenience functions. The duckdb_column_

data/duckdb_nullmask_data functions directly hand you a pointer to the result arrays in

columnar format, and can therefore be very fast. The duckdb_value functions perform bounds‑

and type‑checking, and will automatically cast values to the desired type. This makes them more

convenient and easier to use, at the expense of being slower.

See the Types page for more information.

Note. For optimal performance, use duckdb_column_data and duckdb_nullmask_

data to extract data from the query result. The duckdb_value functions perform internal

DuckDB Documentation

type‑checking, bounds‑checking and casting which makes them slower.

duckdb_value Below is an examplethat prints the above result toCSVformat using the duckdb_

value_varchar function. Note that the function is generic: we do not need to know about the

types of the individual result columns.

// print the above result to CSV format using `duckdb_value_varchar`

idx_t row_count = duckdb_row_count(&result);

idx_t column_count = duckdb_column_count(&result);

for(idx_t row = 0; row < row_count; row++) {

for(idx_t col = 0; col < column_count; col++) {

if (col > 0) printf(",");

auto str_val = duckdb_value_varchar(&result, col, row);

printf("%s", str_val);

duckdb_free(str_val);

}

printf("\n");

}

duckdb_column_data Below is an example that prints the above result to CSV format using the

duckdb_column_datafunction. Note that the function is NOTgeneric: we do need to know exactly

what the types of the result columns are.

int32_t *i_data = (int32_t *) duckdb_column_data(&result, 0);

int32_t *j_data = (int32_t *) duckdb_column_data(&result, 1);

bool *i_mask = duckdb_nullmask_data(&result, 0);

bool *j_mask = duckdb_nullmask_data(&result, 1);

idx_t row_count = duckdb_row_count(&result);

for(idx_t row = 0; row < row_count; row++) {

if (i_mask[row]) {

printf("NULL");

} else {

printf("%d", i_data[row]);

}

printf(",");

if (j_mask[row]) {

printf("NULL");

} else {

printf("%d", j_data[row]);

}

printf("\n");

}

DuckDB Documentation

Note. When using duckdb_column_data, be careful that the type matches exactly what

you expect it to be. As the code directly accesses an internal array, there is no type‑checking.

Accessing a DUCKDB_TYPE_INTEGER column as if it was a DUCKDB_TYPE_BIGINT column

will provide unpredictable results!

API Reference

duckdb_state duckdb_query(duckdb_connection connection, const char *query,

duckdb_result *out_result);

void duckdb_destroy_result(duckdb_result *result);

const char *duckdb_column_name(duckdb_result *result, idx_t col);

duckdb_type duckdb_column_type(duckdb_result *result, idx_t col);

duckdb_logical_type duckdb_column_logical_type(duckdb_result *result, idx_t

col);

idx_t duckdb_column_count(duckdb_result *result);

idx_t duckdb_row_count(duckdb_result *result);

idx_t duckdb_rows_changed(duckdb_result *result);

void *duckdb_column_data(duckdb_result *result, idx_t col);

bool *duckdb_nullmask_data(duckdb_result *result, idx_t col);

const char *duckdb_result_error(duckdb_result *result);

duckdb_query Executes a SQL query within a connection and stores the full (materialized) result

in the out_result pointer. If the query fails to execute, DuckDBError is returned and the error message

can be retrieved by calling duckdb_result_error.

Note that aer running duckdb_query, duckdb_destroy_result must be called on the result

object even if the query fails, otherwise the error stored within the result will not be freed correctly.

Syntax

duckdb_state duckdb_query(

duckdb_connection connection,

const char *query,

duckdb_result *out_result

);

Parameters

• connection

The connection to perform the query in.

DuckDB Documentation

• query

The SQL query to run.

• out_result

The query result.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_destroy_result Closes the result and de‑allocates all memory allocated for that con‑

nection.

Syntax

void duckdb_destroy_result(

duckdb_result *result

);

Parameters

• result

The result to destroy.

duckdb_column_name Returns the column name of the specified column. The result should not

need be freed; the column names will automatically be destroyed when the result is destroyed.

Returns NULL if the column is out of range.

Syntax

const char *duckdb_column_name(

duckdb_result *result,

idx_t col

);

DuckDB Documentation

Parameters

• result

The result object to fetch the column name from.

• col

The column index.

• returns

The column name of the specified column.

duckdb_column_type Returns the column type of the specified column.

Returns DUCKDB_TYPE_INVALID if the column is out of range.

Syntax

duckdb_type duckdb_column_type(

duckdb_result *result,

idx_t col

);

Parameters

• result

The result object to fetch the column type from.

• col

The column index.

• returns

The column type of the specified column.

duckdb_column_logical_type Returns the logical column type of the specified column.

The return type of this call should be destroyed with duckdb_destroy_logical_type.

Returns NULL if the column is out of range.

DuckDB Documentation

Syntax

duckdb_logical_type duckdb_column_logical_type(

duckdb_result *result,

idx_t col

);

Parameters

• result

The result object to fetch the column type from.

• col

The column index.

• returns

The logical column type of the specified column.

duckdb_column_count Returns the number of columns present in a the result object.

Syntax

idx_t duckdb_column_count(

duckdb_result *result

);

Parameters

• result

The result object.

• returns

The number of columns present in the result object.

duckdb_row_count Returns the number of rows present in a the result object.

DuckDB Documentation

Syntax

idx_t duckdb_row_count(

duckdb_result *result

);

Parameters

• result

The result object.

• returns

The number of rows present in the result object.

duckdb_rows_changed Returns the number of rows changed by the query stored in the result.

This is relevant only for INSERT/UPDATE/DELETE queries. For other queries the rows_changed will be

Syntax

idx_t duckdb_rows_changed(

duckdb_result *result

);

Parameters

• result

The result object.

• returns

The number of rows changed.

duckdb_column_data DEPRECATED: Prefer using duckdb_result_get_chunk instead.

Returns the data of a specific column of a result in columnar format.

The function returns a dense array which contains the result data. The exact type stored in the array

depends on the corresponding duckdb_type(as provided by duckdb_column_type). Forthe exact

type by which the data should be accessed, see the comments in the types section or the DUCKDB_

TYPE enum.

DuckDB Documentation

For example, for a column of type DUCKDB_TYPE_INTEGER, rows can be accessed in the following

manner:

int32_t *data = (int32_t *) duckdb_column_data(&result, 0);

printf("Data for row %d: %d\n", row, data[row]);

Syntax

void *duckdb_column_data(

duckdb_result *result,

idx_t col

);

Parameters

• result

The result object to fetch the column data from.

• col

The column index.

• returns

The column data of the specified column.

duckdb_nullmask_data DEPRECATED: Prefer using duckdb_result_get_chunk in‑

stead.

Returns the nullmask of a specific column of a result in columnar format. The nullmask indicates for

every row whether or not the corresponding row is NULL. If a row is NULL, the values present in the

array provided by duckdb_column_data are undefined.

int32_t *data = (int32_t *) duckdb_column_data(&result, 0);

bool *nullmask = duckdb_nullmask_data(&result, 0);

if (nullmask[row]) {

printf("Data for row %d: NULL\n", row);

} else {

printf("Data for row %d: %d\n", row, data[row]);

}

DuckDB Documentation

Syntax

bool *duckdb_nullmask_data(

duckdb_result *result,

idx_t col

);

Parameters

• result

The result object to fetch the nullmask from.

• col

The column index.

• returns

The nullmask of the specified column.

duckdb_result_error Returns the error message contained within the result. The error is only

set if duckdb_query returns DuckDBError.

The result of this function must not be freed. It will be cleaned up when duckdb_destroy_result

is called.

Syntax

const char *duckdb_result_error(

duckdb_result *result

);

Parameters

• result

The result object to fetch the error from.

• returns

The error of the result.

DuckDB Documentation

C API ‑ Data Chunks

Data chunks represent a horizontal slice of a table. They hold a number of vectors, that can each hold

up to the VECTOR_SIZE rows. The vector size can be obtained through the duckdb_vector_

size function and is configurable, but is usually set to 2048.

Data chunks and vectors are what DuckDB uses natively to store and represent data. For this reason,

the data chunk interface is the most eicient way of interfacing with DuckDB. Be aware, however, that

correctly interfacing with DuckDB using the data chunk API does require knowledge of DuckDB's in‑

ternal vector format.

The primary manner of interfacing with data chunks is by obtaining the internal vectors of the

data chunk using the duckdb_data_chunk_get_vector method, and subsequently using

the duckdb_vector_get_data and duckdb_vector_get_validity methods to read

the internal data and the validity mask of the vector. For composite types (list and struct vectors),

duckdb_list_vector_get_child and duckdb_struct_vector_get_child should be

used to read child vectors.

API Reference

duckdb_data_chunk duckdb_create_data_chunk(duckdb_logical_type *types, idx_t

column_count);

void duckdb_destroy_data_chunk(duckdb_data_chunk *chunk);

void duckdb_data_chunk_reset(duckdb_data_chunk chunk);

idx_t duckdb_data_chunk_get_column_count(duckdb_data_chunk chunk);

duckdb_vector duckdb_data_chunk_get_vector(duckdb_data_chunk chunk, idx_t

col_idx);

idx_t duckdb_data_chunk_get_size(duckdb_data_chunk chunk);

void duckdb_data_chunk_set_size(duckdb_data_chunk chunk, idx_t size);

Vector Interface

duckdb_logical_type duckdb_vector_get_column_type(duckdb_vector vector);

void *duckdb_vector_get_data(duckdb_vector vector);

uint64_t *duckdb_vector_get_validity(duckdb_vector vector);

void duckdb_vector_ensure_validity_writable(duckdb_vector vector);

void duckdb_vector_assign_string_element(duckdb_vector vector, idx_t index,

const char *str);

void duckdb_vector_assign_string_element_len(duckdb_vector vector, idx_t

index, const char *str, idx_t str_len);

duckdb_vector duckdb_list_vector_get_child(duckdb_vector vector);

DuckDB Documentation

idx_t duckdb_list_vector_get_size(duckdb_vector vector);

duckdb_state duckdb_list_vector_set_size(duckdb_vector vector, idx_t size);

duckdb_state duckdb_list_vector_reserve(duckdb_vector vector, idx_t

required_capacity);

duckdb_vector duckdb_struct_vector_get_child(duckdb_vector vector, idx_t

index);

Validity Mask Functions

bool duckdb_validity_row_is_valid(uint64_t *validity, idx_t row);

void duckdb_validity_set_row_validity(uint64_t *validity, idx_t row, bool

valid);

void duckdb_validity_set_row_invalid(uint64_t *validity, idx_t row);

void duckdb_validity_set_row_valid(uint64_t *validity, idx_t row);

duckdb_create_data_chunk Creates an empty DataChunk with the specified set of types.

Syntax

duckdb_data_chunk duckdb_create_data_chunk(

duckdb_logical_type *types,

idx_t column_count

);

Parameters

• types

An array of types of the data chunk.

• column_count

The number of columns.

• returns

The data chunk.

duckdb_destroy_data_chunk Destroys the data chunk and de‑allocates all memory allo‑

cated for that chunk.

DuckDB Documentation

Syntax

void duckdb_destroy_data_chunk(

duckdb_data_chunk *chunk

);

Parameters

• chunk

The data chunk to destroy.

duckdb_data_chunk_reset Resets a data chunk, clearing the validity masks and setting the

cardinality of the data chunk to 0.

Syntax

void duckdb_data_chunk_reset(

duckdb_data_chunk chunk

);

Parameters

• chunk

The data chunk to reset.

duckdb_data_chunk_get_column_count Retrieves the number of columns in a data

chunk.

Syntax

idx_t duckdb_data_chunk_get_column_count(

duckdb_data_chunk chunk

);

Parameters

• chunk

The data chunk to get the data from

DuckDB Documentation

• returns

The number of columns in the data chunk

duckdb_data_chunk_get_vector Retrieves the vector at the specified column index in the

data chunk.

The pointer to the vector is valid for as long as the chunk is alive. It does NOT need to be destroyed.

Syntax

duckdb_vector duckdb_data_chunk_get_vector(

duckdb_data_chunk chunk,

idx_t col_idx

);

Parameters

• chunk

The data chunk to get the data from

• returns

The vector

duckdb_data_chunk_get_size Retrieves the current number of tuples in a data chunk.

Syntax

idx_t duckdb_data_chunk_get_size(

duckdb_data_chunk chunk

);

Parameters

• chunk

The data chunk to get the data from

• returns

The number of tuples in the data chunk

DuckDB Documentation

duckdb_data_chunk_set_size Sets the current number of tuples in a data chunk.

Syntax

void duckdb_data_chunk_set_size(

duckdb_data_chunk chunk,

idx_t size

);

Parameters

• chunk

The data chunk to set the size in

• size

The number of tuples in the data chunk

duckdb_vector_get_column_type Retrieves the column type of the specified vector.

The result must be destroyed with duckdb_destroy_logical_type.

Syntax

duckdb_logical_type duckdb_vector_get_column_type(

duckdb_vector vector

);

Parameters

• vector

The vector get the data from

• returns

The type of the vector

duckdb_vector_get_data Retrieves the data pointer of the vector.

The data pointer can be used to read or write values from the vector. How to read or write values

depends on the type of the vector.

DuckDB Documentation

Syntax

void *duckdb_vector_get_data(

duckdb_vector vector

);

Parameters

• vector

The vector to get the data from

• returns

The data pointer

duckdb_vector_get_validity Retrieves the validity mask pointer of the specified vector.

If all values are valid, this function MIGHT return NULL!

The validity mask is a bitset that signifies null‑ness within the data chunk. It is a series of uint64_t

values, where each uint64_t value contains validity for 64 tuples. The bit is set to 1 if the value is valid

(i.e., not NULL) or 0 if the value is invalid (i.e., NULL).

Validity of a specific value can be obtained like this:

idx_t entry_idx = row_idx / 64; idx_t idx_in_entry = row_idx % 64; bool is_valid = validity_mask[entry_

idx] & (1 « idx_in_entry);

Alternatively, the (slower) duckdb_validity_row_is_valid function can be used.

Syntax

uint64_t *duckdb_vector_get_validity(

duckdb_vector vector

);

Parameters

• vector

The vector to get the data from

• returns

The pointer to the validity mask, or NULL if no validity mask is present

DuckDB Documentation

duckdb_vector_ensure_validity_writable Ensures the validity mask is writable by al‑

locating it.

Aer this function is called, duckdb_vector_get_validity will ALWAYS return non‑NULL. This

allows null values to be written to the vector, regardless of whether a validity mask was present be‑

fore.

Syntax

void duckdb_vector_ensure_validity_writable(

duckdb_vector vector

);

Parameters

• vector

The vector to alter

duckdb_vector_assign_string_element Assigns a string element in the vector at the

specified location.

Syntax

void duckdb_vector_assign_string_element(

duckdb_vector vector,

idx_t index,

const char *str

);

Parameters

• vector

The vector to alter

• index

The row position in the vector to assign the string to

• str

The null‑terminated string

DuckDB Documentation

duckdb_vector_assign_string_element_len Assigns a string element in the vector at

the specified location.

Syntax

void duckdb_vector_assign_string_element_len(

duckdb_vector vector,

idx_t index,

const char *str,

idx_t str_len

);

Parameters

• vector

The vector to alter

• index

The row position in the vector to assign the string to

• str

The string

• str_len

The length of the string (in bytes)

duckdb_list_vector_get_child Retrieves the child vector of a list vector.

The resulting vector is valid as long as the parent vector is valid.

Syntax

duckdb_vector duckdb_list_vector_get_child(

duckdb_vector vector

);

DuckDB Documentation

Parameters

• vector

The vector

• returns

The child vector

duckdb_list_vector_get_size Returns the size of the child vector of the list

Syntax

idx_t duckdb_list_vector_get_size(

duckdb_vector vector

);

Parameters

• vector

The vector

• returns

The size of the child list

duckdb_list_vector_set_size Sets the total size of the underlying child‑vector of a list vec‑

tor.

Syntax

duckdb_state duckdb_list_vector_set_size(

duckdb_vector vector,

idx_t size

);

Parameters

• vector

The list vector.

DuckDB Documentation

• size

The size of the child list.

• returns

The duckdb state. Returns DuckDBError if the vector is nullptr.

duckdb_list_vector_reserve Sets the total capacity of the underlying child‑vector of a

list.

Syntax

duckdb_state duckdb_list_vector_reserve(

duckdb_vector vector,

idx_t required_capacity

);

Parameters

• vector

The list vector.

• required_capacity

the total capacity to reserve.

• return

The duckdb state. Returns DuckDBError if the vector is nullptr.

duckdb_struct_vector_get_child Retrieves the child vector of a struct vector.

The resulting vector is valid as long as the parent vector is valid.

Syntax

duckdb_vector duckdb_struct_vector_get_child(

duckdb_vector vector,

idx_t index

);

DuckDB Documentation

Parameters

• vector

The vector

• index

The child index

• returns

The child vector

duckdb_validity_row_is_valid Returns whether or not a row is valid (i.e., not NULL) in the

given validity mask.

Syntax

bool duckdb_validity_row_is_valid(

uint64_t *validity,

idx_t row

);

Parameters

• validity

The validity mask, as obtained through

duckdb_vector_get_validity

• row

The row index

• returns

true if the row is valid, false otherwise

duckdb_validity_set_row_validity In a validity mask, sets a specific row to either valid

or invalid.

Note that duckdb_vector_ensure_validity_writable should be called before calling

duckdb_vector_get_validity, to ensure that there is a validity mask to write to.

DuckDB Documentation

Syntax

void duckdb_validity_set_row_validity(

uint64_t *validity,

idx_t row,

bool valid

);

Parameters

• validity

The validity mask, as obtained through duckdb_vector_get_validity.

• row

The row index

• valid

Whether or not to set the row to valid, or invalid

duckdb_validity_set_row_invalid In a validity mask, sets a specific row to invalid.

Equivalent to duckdb_validity_set_row_validity with valid set to false.

Syntax

void duckdb_validity_set_row_invalid(

uint64_t *validity,

idx_t row

);

Parameters

• validity

The validity mask

• row

The row index

duckdb_validity_set_row_valid In a validity mask, sets a specific row to valid.

Equivalent to duckdb_validity_set_row_validity with valid set to true.

DuckDB Documentation

Syntax

void duckdb_validity_set_row_valid(

uint64_t *validity,

idx_t row

);

Parameters

• validity

The validity mask

• row

The row index

C API ‑ Values

The value class represents a single value of any type.

API Reference

void duckdb_destroy_value(duckdb_value *value);

duckdb_value duckdb_create_varchar(const char *text);

duckdb_value duckdb_create_varchar_length(const char *text, idx_t length);

duckdb_value duckdb_create_int64(int64_t val);

char *duckdb_get_varchar(duckdb_value value);

int64_t duckdb_get_int64(duckdb_value value);

duckdb_destroy_value Destroys the value and de‑allocates all memory allocated for that

type.

Syntax

void duckdb_destroy_value(

duckdb_value *value

);

DuckDB Documentation

Parameters

• value

The value to destroy.

duckdb_create_varchar Creates a value from a null‑terminated string

Syntax

duckdb_value duckdb_create_varchar(

const char *text

);

Parameters

• value

The null‑terminated string

• returns

The value. This must be destroyed with duckdb_destroy_value.

duckdb_create_varchar_length Creates a value from a string

Syntax

duckdb_value duckdb_create_varchar_length(

const char *text,

idx_t length

);

Parameters

• value

The text

• length

The length of the text

• returns

The value. This must be destroyed with duckdb_destroy_value.

DuckDB Documentation

duckdb_create_int64 Creates a value from an int64

Syntax

duckdb_value duckdb_create_int64(

int64_t val

);

Parameters

• value

The bigint value

• returns

The value. This must be destroyed with duckdb_destroy_value.

duckdb_get_varchar Obtains a string representation of the given value. The result must be

destroyed with duckdb_free.

Syntax

char *duckdb_get_varchar(

duckdb_value value

);

Parameters

• value

The value

• returns

The string value. This must be destroyed with duckdb_free.

duckdb_get_int64 Obtains an int64 of the given value.

DuckDB Documentation

Syntax

int64_t duckdb_get_int64(

duckdb_value value

);

Parameters

• value

The value

• returns

The int64 value, or 0 if no conversion is possible

C API ‑ Types

DuckDB is a strongly typed database system. As such, every column has a single type specified. This

type is constant over the entire column. That is to say, a column that is labeled as an INTEGERcolumn

will only contain INTEGER values.

DuckDB also supports columns of composite types. For example, it is possible to define an array of

integers (INT[]). It is also possible to define types as arbitrary structs (ROW(i INTEGER, j VAR-

CHAR)). For that reason, native DuckDB type objects are not mere enums, but a class that can poten‑

tially be nested.

Types in the C API are modeled using an enum (duckdb_type) and a complex class (duckdb_

logical_type). For most primitive types, e.g., integers or varchars, the enum is suicient. For

more complex types, such as lists, structs or decimals, the logical type must be used.

typedef enum DUCKDB_TYPE {

DUCKDB_TYPE_INVALID,

DUCKDB_TYPE_BOOLEAN,

DUCKDB_TYPE_TINYINT,

DUCKDB_TYPE_SMALLINT,

DUCKDB_TYPE_INTEGER,

DUCKDB_TYPE_BIGINT,

DUCKDB_TYPE_UTINYINT,

DUCKDB_TYPE_USMALLINT,

DUCKDB_TYPE_UINTEGER,

DUCKDB_TYPE_UBIGINT,

DUCKDB_TYPE_FLOAT,

DUCKDB_TYPE_DOUBLE,

DuckDB Documentation

DUCKDB_TYPE_TIMESTAMP,

DUCKDB_TYPE_DATE,

DUCKDB_TYPE_TIME,

DUCKDB_TYPE_INTERVAL,

DUCKDB_TYPE_HUGEINT,

DUCKDB_TYPE_VARCHAR,

DUCKDB_TYPE_BLOB,

DUCKDB_TYPE_DECIMAL,

DUCKDB_TYPE_TIMESTAMP_S,

DUCKDB_TYPE_TIMESTAMP_MS,

DUCKDB_TYPE_TIMESTAMP_NS,

DUCKDB_TYPE_ENUM,

DUCKDB_TYPE_LIST,

DUCKDB_TYPE_STRUCT,

DUCKDB_TYPE_MAP,

DUCKDB_TYPE_UUID,

DUCKDB_TYPE_UNION,

DUCKDB_TYPE_BIT,

} duckdb_type;

Functions

The enum type of a column in the result can be obtained using the duckdb_column_type func‑

tion. The logical type of a column can be obtained using the duckdb_column_logical_type

function.

duckdb_value The duckdb_value functions will auto‑cast values as required. For example, it

is no problem to use duckdb_value_double on a column of type duckdb_value_int32. The

value will be auto‑cast and returned as a double. Note that in certain cases the cast may fail. For

example, this can happen if we request a duckdb_value_int8 and the value does not fit within

an int8value. In this case, a default value will be returned (usually 0or nullptr). The same default

value will also be returned if the corresponding value is NULL.

The duckdb_value_is_null function can be used to check if a specific value is NULL or not.

The exception to the auto‑cast rule is the duckdb_value_varchar_internal function. This

function does not auto‑cast and only works for VARCHAR columns. The reason this function exists is

that the result does not need to be freed.

Note. Note that duckdb_value_varchar and duckdb_value_blob require the result

to be de‑allocated using duckdb_free.

DuckDB Documentation

duckdb_result_get_chunk The duckdb_result_get_chunk function can be used to

read data chunks from a DuckDB result set, and is the most eicient way of reading data from a

DuckDB result using the C API. It is also the only way of reading data of certain types from a DuckDB

result. For example, the duckdb_value functions do not support structural reading of composite

types (lists or structs) or more complex types like enums and decimals.

For more information about data chunks, see the documentation on data chunks.

API Reference

duckdb_data_chunk duckdb_result_get_chunk(duckdb_result result, idx_t chunk_

index);

bool duckdb_result_is_streaming(duckdb_result result);

idx_t duckdb_result_chunk_count(duckdb_result result);

bool duckdb_value_boolean(duckdb_result *result, idx_t col, idx_t row);

int8_t duckdb_value_int8(duckdb_result *result, idx_t col, idx_t row);

int16_t duckdb_value_int16(duckdb_result *result, idx_t col, idx_t row);

int32_t duckdb_value_int32(duckdb_result *result, idx_t col, idx_t row);

int64_t duckdb_value_int64(duckdb_result *result, idx_t col, idx_t row);

duckdb_hugeint duckdb_value_hugeint(duckdb_result *result, idx_t col, idx_t

row);

duckdb_decimal duckdb_value_decimal(duckdb_result *result, idx_t col, idx_t

row);

uint8_t duckdb_value_uint8(duckdb_result *result, idx_t col, idx_t row);

uint16_t duckdb_value_uint16(duckdb_result *result, idx_t col, idx_t row);

uint32_t duckdb_value_uint32(duckdb_result *result, idx_t col, idx_t row);

uint64_t duckdb_value_uint64(duckdb_result *result, idx_t col, idx_t row);

float duckdb_value_float(duckdb_result *result, idx_t col, idx_t row);

double duckdb_value_double(duckdb_result *result, idx_t col, idx_t row);

duckdb_date duckdb_value_date(duckdb_result *result, idx_t col, idx_t row);

duckdb_time duckdb_value_time(duckdb_result *result, idx_t col, idx_t row);

duckdb_timestamp duckdb_value_timestamp(duckdb_result *result, idx_t col,

idx_t row);

duckdb_interval duckdb_value_interval(duckdb_result *result, idx_t col, idx_

t row);

char *duckdb_value_varchar(duckdb_result *result, idx_t col, idx_t row);

char *duckdb_value_varchar_internal(duckdb_result *result, idx_t col, idx_t

row);

duckdb_string duckdb_value_string_internal(duckdb_result *result, idx_t col,

idx_t row);

duckdb_blob duckdb_value_blob(duckdb_result *result, idx_t col, idx_t row);

bool duckdb_value_is_null(duckdb_result *result, idx_t col, idx_t row);

DuckDB Documentation

Date/Time/Timestamp Helpers

duckdb_date_struct duckdb_from_date(duckdb_date date);

duckdb_date duckdb_to_date(duckdb_date_struct date);

duckdb_time_struct duckdb_from_time(duckdb_time time);

duckdb_time duckdb_to_time(duckdb_time_struct time);

duckdb_timestamp_struct duckdb_from_timestamp(duckdb_timestamp ts);

duckdb_timestamp duckdb_to_timestamp(duckdb_timestamp_struct ts);

Hugeint Helpers

double duckdb_hugeint_to_double(duckdb_hugeint val);

duckdb_hugeint duckdb_double_to_hugeint(double val);

duckdb_decimal duckdb_double_to_decimal(double val, uint8_t width, uint8_t

scale);

Decimal Helpers

double duckdb_decimal_to_double(duckdb_decimal val);

Logical Type Interface

duckdb_logical_type duckdb_create_logical_type(duckdb_type type);

duckdb_logical_type duckdb_create_list_type(duckdb_logical_type type);

duckdb_logical_type duckdb_create_map_type(duckdb_logical_type key_type,

duckdb_logical_type value_type);

duckdb_logical_type duckdb_create_union_type(duckdb_logical_type member_

types, const char **member_names, idx_t member_count);

duckdb_logical_type duckdb_create_struct_type(duckdb_logical_type *member_

types, const char **member_names, idx_t member_count);

duckdb_logical_type duckdb_create_decimal_type(uint8_t width, uint8_t

scale);

duckdb_type duckdb_get_type_id(duckdb_logical_type type);

uint8_t duckdb_decimal_width(duckdb_logical_type type);

uint8_t duckdb_decimal_scale(duckdb_logical_type type);

duckdb_type duckdb_decimal_internal_type(duckdb_logical_type type);

duckdb_type duckdb_enum_internal_type(duckdb_logical_type type);

uint32_t duckdb_enum_dictionary_size(duckdb_logical_type type);

char *duckdb_enum_dictionary_value(duckdb_logical_type type, idx_t index);

duckdb_logical_type duckdb_list_type_child_type(duckdb_logical_type type);

duckdb_logical_type duckdb_map_type_key_type(duckdb_logical_type type);

duckdb_logical_type duckdb_map_type_value_type(duckdb_logical_type type);

idx_t duckdb_struct_type_child_count(duckdb_logical_type type);

DuckDB Documentation

char *duckdb_struct_type_child_name(duckdb_logical_type type, idx_t index);

duckdb_logical_type duckdb_struct_type_child_type(duckdb_logical_type type,

idx_t index);

idx_t duckdb_union_type_member_count(duckdb_logical_type type);

char *duckdb_union_type_member_name(duckdb_logical_type type, idx_t index);

duckdb_logical_type duckdb_union_type_member_type(duckdb_logical_type type,

idx_t index);

void duckdb_destroy_logical_type(duckdb_logical_type *type);

duckdb_result_get_chunk Fetches a data chunk from the duckdb_result. This function

should be called repeatedly until the result is exhausted.

The result must be destroyed with duckdb_destroy_data_chunk.

This function supersedes all duckdb_value functions, as well as the duckdb_column_dataand

duckdb_nullmask_data functions. It results in significantly better performance, and should be

preferred in newer code‑bases.

If this function is used, none of the other result functions can be used and vice versa (i.e., this function

cannot be mixed with the legacy result functions).

Use duckdb_result_chunk_count to figure out how many chunks there are in the result.

Syntax

duckdb_data_chunk duckdb_result_get_chunk(

duckdb_result result,

idx_t chunk_index

);

Parameters

• result

The result object to fetch the data chunk from.

• chunk_index

The chunk index to fetch from.

• returns

The resulting data chunk. Returns NULL if the chunk index is out of bounds.

DuckDB Documentation

duckdb_result_is_streaming Checks if the type of the internal result is StreamQueryRe‑

sult.

Syntax

bool duckdb_result_is_streaming(

duckdb_result result

);

Parameters

• result

The result object to check.

• returns

Whether or not the result object is of the type StreamQueryResult

duckdb_result_chunk_count Returns the number of data chunks present in the result.

Syntax

idx_t duckdb_result_chunk_count(

duckdb_result result

);

Parameters

• result

The result object

• returns

Number of data chunks present in the result.

duckdb_value_boolean

DuckDB Documentation

Syntax

bool duckdb_value_boolean(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The boolean value at the specified location, or false if the value cannot be converted.

duckdb_value_int8

Syntax

int8_t duckdb_value_int8(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The int8_t value at the specified location, or 0 if the value cannot be converted.

duckdb_value_int16

Syntax

int16_t duckdb_value_int16(

duckdb_result *result,

idx_t col,

idx_t row

);

DuckDB Documentation

Parameters

• returns

The int16_t value at the specified location, or 0 if the value cannot be converted.

duckdb_value_int32

Syntax

int32_t duckdb_value_int32(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The int32_t value at the specified location, or 0 if the value cannot be converted.

duckdb_value_int64

Syntax

int64_t duckdb_value_int64(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The int64_t value at the specified location, or 0 if the value cannot be converted.

duckdb_value_hugeint

DuckDB Documentation

Syntax

duckdb_hugeint duckdb_value_hugeint(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The duckdb_hugeint value at the specified location, or 0 if the value cannot be converted.

duckdb_value_decimal

Syntax

duckdb_decimal duckdb_value_decimal(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The duckdb_decimal value at the specified location, or 0 if the value cannot be converted.

duckdb_value_uint8

Syntax

uint8_t duckdb_value_uint8(

duckdb_result *result,

idx_t col,

idx_t row

);

DuckDB Documentation

Parameters

• returns

The uint8_t value at the specified location, or 0 if the value cannot be converted.

duckdb_value_uint16

Syntax

uint16_t duckdb_value_uint16(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The uint16_t value at the specified location, or 0 if the value cannot be converted.

duckdb_value_uint32

Syntax

uint32_t duckdb_value_uint32(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The uint32_t value at the specified location, or 0 if the value cannot be converted.

duckdb_value_uint64

DuckDB Documentation

Syntax

uint64_t duckdb_value_uint64(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The uint64_t value at the specified location, or 0 if the value cannot be converted.

duckdb_value_float

Syntax

float duckdb_value_float(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The float value at the specified location, or 0 if the value cannot be converted.

duckdb_value_double

Syntax

double duckdb_value_double(

duckdb_result *result,

idx_t col,

idx_t row

);

DuckDB Documentation

Parameters

• returns

The double value at the specified location, or 0 if the value cannot be converted.

duckdb_value_date

Syntax

duckdb_date duckdb_value_date(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The duckdb_date value at the specified location, or 0 if the value cannot be converted.

duckdb_value_time

Syntax

duckdb_time duckdb_value_time(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The duckdb_time value at the specified location, or 0 if the value cannot be converted.

duckdb_value_timestamp

DuckDB Documentation

Syntax

duckdb_timestamp duckdb_value_timestamp(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The duckdb_timestamp value at the specified location, or 0 if the value cannot be converted.

duckdb_value_interval

Syntax

duckdb_interval duckdb_value_interval(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The duckdb_interval value at the specified location, or 0 if the value cannot be converted.

duckdb_value_varchar

Syntax

char *duckdb_value_varchar(

duckdb_result *result,

idx_t col,

idx_t row

);

DuckDB Documentation

Parameters

• DEPRECATED

use duckdb_value_string instead. This function does not work correctly if the string contains null

bytes.

• returns

The text value at the specified location as a null‑terminated string, or nullptr if the value cannot be

converted. The result must be freed with duckdb_free.

duckdb_value_varchar_internal

Syntax

char *duckdb_value_varchar_internal(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• DEPRECATED

use duckdb_value_string_internal instead. This function does not work correctly if the string contains

null bytes.

• returns

The char* value at the specified location. ONLY works on VARCHAR columns and does not auto‑cast.

If the column is NOT a VARCHAR column this function will return NULL.

The result must NOT be freed.

duckdb_value_string_internal

Syntax

duckdb_string duckdb_value_string_internal(

duckdb_result *result,

idx_t col,

DuckDB Documentation

idx_t row

);

Parameters

• DEPRECATED

use duckdb_value_string_internal instead. This function does not work correctly if the string contains

null bytes.

• returns

The char* value at the specified location. ONLY works on VARCHAR columns and does not auto‑cast.

If the column is NOT a VARCHAR column this function will return NULL.

The result must NOT be freed.

duckdb_value_blob

Syntax

duckdb_blob duckdb_value_blob(

duckdb_result *result,

idx_t col,

idx_t row

);

Parameters

• returns

The duckdb_blob value at the specified location. Returns a blob with blob.data set to nullptr if the

value cannot be converted. The resulting ”blob.data” must be freed with duckdb_free.

duckdb_value_is_null

Syntax

bool duckdb_value_is_null(

duckdb_result *result,

idx_t col,

idx_t row

);

DuckDB Documentation

Parameters

• returns

Returns true if the value at the specified index is NULL, and false otherwise.

duckdb_from_date Decompose a duckdb_date object into year, month and date (stored as

duckdb_date_struct).

Syntax

duckdb_date_struct duckdb_from_date(

duckdb_date date

);

Parameters

• date

The date object, as obtained from a DUCKDB_TYPE_DATE column.

• returns

The duckdb_date_struct with the decomposed elements.

duckdb_to_date Re‑compose a duckdb_date from year, month and date (duckdb_date_

struct).

Syntax

duckdb_date duckdb_to_date(

duckdb_date_struct date

);

Parameters

• date

The year, month and date stored in a duckdb_date_struct.

• returns

The duckdb_date element.

DuckDB Documentation

duckdb_from_time Decompose a duckdb_time object into hour, minute, second and

microsecond (stored as duckdb_time_struct).

Syntax

duckdb_time_struct duckdb_from_time(

duckdb_time time

);

Parameters

• time

The time object, as obtained from a DUCKDB_TYPE_TIME column.

• returns

The duckdb_time_struct with the decomposed elements.

duckdb_to_time Re‑compose a duckdb_time from hour, minute, second and microsecond

(duckdb_time_struct).

Syntax

duckdb_time duckdb_to_time(

duckdb_time_struct time

);

Parameters

• time

The hour, minute, second and microsecond in a duckdb_time_struct.

• returns

The duckdb_time element.

duckdb_from_timestamp Decompose a duckdb_timestamp object into a duckdb_

timestamp_struct.

DuckDB Documentation

Syntax

duckdb_timestamp_struct duckdb_from_timestamp(

duckdb_timestamp ts

);

Parameters

• ts

The ts object, as obtained from a DUCKDB_TYPE_TIMESTAMP column.

• returns

The duckdb_timestamp_struct with the decomposed elements.

duckdb_to_timestamp Re‑compose a duckdb_timestamp from a duckdb_timestamp_

struct.

Syntax

duckdb_timestamp duckdb_to_timestamp(

duckdb_timestamp_struct ts

);

Parameters

• ts

The de‑composed elements in a duckdb_timestamp_struct.

• returns

The duckdb_timestamp element.

duckdb_hugeint_to_double Converts a duckdb_hugeint object (as obtained from a

DUCKDB_TYPE_HUGEINT column) into a double.

Syntax

double duckdb_hugeint_to_double(

duckdb_hugeint val

);

DuckDB Documentation

Parameters

• val

The hugeint value.

• returns

The converted double element.

duckdb_double_to_hugeint Converts a double value to a duckdb_hugeint object.

If the conversion fails because the double value is too big the result will be 0.

Syntax

duckdb_hugeint duckdb_double_to_hugeint(

double val

);

Parameters

• val

The double value.

• returns

The converted duckdb_hugeint element.

duckdb_double_to_decimal Converts a double value to a duckdb_decimal object.

If the conversion fails because the double value is too big, or the width/scale are invalid the result will

be 0.

Syntax

duckdb_decimal duckdb_double_to_decimal(

double val,

uint8_t width,

uint8_t scale

);

DuckDB Documentation

Parameters

• val

The double value.

• returns

The converted duckdb_decimal element.

duckdb_decimal_to_double Converts a duckdb_decimal object (as obtained from a

DUCKDB_TYPE_DECIMAL column) into a double.

Syntax

double duckdb_decimal_to_double(

duckdb_decimal val

);

Parameters

• val

The decimal value.

• returns

The converted double element.

duckdb_create_logical_type Creates a duckdb_logical_type from a standard primi‑

tive type. The resulting type should be destroyed with duckdb_destroy_logical_type.

This should not be used with DUCKDB_TYPE_DECIMAL.

Syntax

duckdb_logical_type duckdb_create_logical_type(

duckdb_type type

);

DuckDB Documentation

Parameters

• type

The primitive type to create.

• returns

The logical type.

duckdb_create_list_type Creates a list type from its child type. The resulting type should

be destroyed with duckdb_destroy_logical_type.

Syntax

duckdb_logical_type duckdb_create_list_type(

duckdb_logical_type type

);

Parameters

• type

The child type of list type to create.

• returns

The logical type.

duckdb_create_map_type Creates a map type from its key type and value type. The resulting

type should be destroyed with duckdb_destroy_logical_type.

Syntax

duckdb_logical_type duckdb_create_map_type(

duckdb_logical_type key_type,

duckdb_logical_type value_type

);

DuckDB Documentation

Parameters

• type

The key type and value type of map type to create.

• returns

The logical type.

duckdb_create_union_type Creates a UNION type from the passed types array The resulting

type should be destroyed with duckdb_destroy_logical_type.

Syntax

duckdb_logical_type duckdb_create_union_type(

duckdb_logical_type member_types,

const char **member_names,

idx_t member_count

);

Parameters

• types

The array of types that the union should consist of.

• type_amount

The size of the types array.

• returns

The logical type.

duckdb_create_struct_type Creates a STRUCT type from the passed member name and

type arrays. The resulting type should be destroyed with duckdb_destroy_logical_type.

Syntax

duckdb_logical_type duckdb_create_struct_type(

duckdb_logical_type *member_types,

const char **member_names,

idx_t member_count

);

100

DuckDB Documentation

Parameters

• member_types

The array of types that the struct should consist of.

• member_names

The array of names that the struct should consist of.

• member_count

The number of members that were specified for both arrays.

• returns

The logical type.

duckdb_create_decimal_type Creates a

duckdb_logical_type

of type decimal with

the specified width and scale The resulting type should be destroyed with duckdb_destroy_

logical_type.

Syntax

duckdb_logical_type duckdb_create_decimal_type(

uint8_t width,

uint8_t scale

);

Parameters

• width

The width of the decimal type

• scale

The scale of the decimal type

• returns

The logical type.

duckdb_get_type_id Retrieves the type class of a duckdb_logical_type.

101

DuckDB Documentation

Syntax

duckdb_type duckdb_get_type_id(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The type id

duckdb_decimal_width Retrieves the width of a decimal type.

Syntax

uint8_t duckdb_decimal_width(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The width of the decimal type

duckdb_decimal_scale Retrieves the scale of a decimal type.

Syntax

uint8_t duckdb_decimal_scale(

duckdb_logical_type type

);

102

DuckDB Documentation

Parameters

• type

The logical type object

• returns

The scale of the decimal type

duckdb_decimal_internal_type Retrieves the internal storage type of a decimal type.

Syntax

duckdb_type duckdb_decimal_internal_type(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The internal type of the decimal type

duckdb_enum_internal_type Retrieves the internal storage type of an enum type.

Syntax

duckdb_type duckdb_enum_internal_type(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The internal type of the enum type

103

DuckDB Documentation

duckdb_enum_dictionary_size Retrieves the dictionary size of the enum type

Syntax

uint32_t duckdb_enum_dictionary_size(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The dictionary size of the enum type

duckdb_enum_dictionary_value Retrieves the dictionary value at the specified position

from the enum.

The result must be freed with duckdb_free

Syntax

char *duckdb_enum_dictionary_value(

duckdb_logical_type type,

idx_t index

);

Parameters

• type

The logical type object

• index

The index in the dictionary

• returns

The string value of the enum type. Must be freed with duckdb_free.

104

DuckDB Documentation

duckdb_list_type_child_type Retrieves the child type of the given list type.

The result must be freed with duckdb_destroy_logical_type

Syntax

duckdb_logical_type duckdb_list_type_child_type(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The child type of the list type. Must be destroyed with duckdb_destroy_logical_type.

duckdb_map_type_key_type Retrieves the key type of the given map type.

The result must be freed with duckdb_destroy_logical_type

Syntax

duckdb_logical_type duckdb_map_type_key_type(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The key type of the map type. Must be destroyed with duckdb_destroy_logical_type.

duckdb_map_type_value_type Retrieves the value type of the given map type.

The result must be freed with duckdb_destroy_logical_type

105

DuckDB Documentation

Syntax

duckdb_logical_type duckdb_map_type_value_type(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The value type of the map type. Must be destroyed with duckdb_destroy_logical_type.

duckdb_struct_type_child_count Returns the number of children of a struct type.

Syntax

idx_t duckdb_struct_type_child_count(

duckdb_logical_type type

);

Parameters

• type

The logical type object

• returns

The number of children of a struct type.

duckdb_struct_type_child_name Retrieves the name of the struct child.

The result must be freed with duckdb_free

Syntax

char *duckdb_struct_type_child_name(

duckdb_logical_type type,

idx_t index

);

106

DuckDB Documentation

Parameters

• type

The logical type object

• index

The child index

• returns

The name of the struct type. Must be freed with duckdb_free.

duckdb_struct_type_child_type Retrieves the child type of the given struct type at the

specified index.

The result must be freed with duckdb_destroy_logical_type

Syntax

duckdb_logical_type duckdb_struct_type_child_type(

duckdb_logical_type type,

idx_t index

);

Parameters

• type

The logical type object

• index

The child index

• returns

The child type of the struct type. Must be destroyed with duckdb_destroy_logical_type.

duckdb_union_type_member_count Returns the number of members that the union type

has.

107

DuckDB Documentation

Syntax

idx_t duckdb_union_type_member_count(

duckdb_logical_type type

);

Parameters

• type

The logical type (union) object

• returns

The number of members of a union type.

duckdb_union_type_member_name Retrieves the name of the union member.

The result must be freed with duckdb_free

Syntax

char *duckdb_union_type_member_name(

duckdb_logical_type type,

idx_t index

);

Parameters

• type

The logical type object

• index

The child index

• returns

The name of the union member. Must be freed with duckdb_free.

duckdb_union_type_member_type Retrievesthe child type of the given union member at the

specified index.

The result must be freed with duckdb_destroy_logical_type

108

DuckDB Documentation

Syntax

duckdb_logical_type duckdb_union_type_member_type(

duckdb_logical_type type,

idx_t index

);

Parameters

• type

The logical type object

• index

The child index

• returns

The child type of the union member. Must be destroyed with duckdb_destroy_logical_

type.

duckdb_destroy_logical_type Destroys the logical type and de‑allocates all memory allo‑

cated for that type.

Syntax

void duckdb_destroy_logical_type(

duckdb_logical_type *type

);

Parameters

• type

The logical type to destroy.

C API ‑ Prepared Statements

A prepared statement is a parameterized query. The query is prepared with question marks (?) or dol‑

lar symbols ($1) indicating the parameters of the query. Values can then be bound to these parame‑

ters, aer which the prepared statement can be executed using those parameters. A single query can

be prepared once and executed many times.

109

DuckDB Documentation

Prepared statements are useful to:

• Easily supply parameters to functions while avoiding string concatenation/SQL injection

attacks.

• Speeding up queries that will be executed many times with dierent parameters.

DuckDB supports prepared statements in the C API with the duckdb_prepare method. The

duckdb_bindfamily of functions is used to supply values for subsequent execution of the prepared

statement using duckdb_execute_prepared. Aer we are done with the prepared statement it

can be cleaned up using the duckdb_destroy_prepare method.

Example

duckdb_prepared_statement stmt;

duckdb_result result;

if (duckdb_prepare(con, "INSERT INTO integers VALUES ($1, $2)", &stmt) ==

DuckDBError) {

// handle error

}

duckdb_bind_int32(stmt, 1, 42); // the parameter index starts counting at 1!

duckdb_bind_int32(stmt, 2, 43);

// NULL as second parameter means no result set is requested

duckdb_execute_prepared(stmt, NULL);

duckdb_destroy_prepare(&stmt);

// we can also query result sets using prepared statements

if (duckdb_prepare(con, "SELECT * FROM integers WHERE i = ?", &stmt) ==

DuckDBError) {

// handle error

}

duckdb_bind_int32(stmt, 1, 42);

duckdb_execute_prepared(stmt, &result);

// do something with result

// clean up

duckdb_destroy_result(&result);

duckdb_destroy_prepare(&stmt);

Aer calling duckdb_prepare, the prepared statement parameters can be inspected using

duckdb_nparams and duckdb_param_type. In case the prepare fails, the error can be

110

DuckDB Documentation

obtained through duckdb_prepare_error.

It is not required that the duckdb_bind family of functions matches the prepared statement param‑

eter type exactly. The values will be auto‑cast to the required value as required. For example, calling

duckdb_bind_int8 on a parameter type of DUCKDB_TYPE_INTEGER will work as expected.

Note. Do not use prepared statements to insert large amounts of data into DuckDB. Instead it

is recommended to use the Appender.

API Reference

duckdb_state duckdb_prepare(duckdb_connection connection, const char *query,

duckdb_prepared_statement *out_prepared_statement);

void duckdb_destroy_prepare(duckdb_prepared_statement *prepared_statement);

const char *duckdb_prepare_error(duckdb_prepared_statement prepared_

statement);

idx_t duckdb_nparams(duckdb_prepared_statement prepared_statement);

const char *duckdb_parameter_name(duckdb_prepared_statement prepared_

statement, idx_t index);

duckdb_type duckdb_param_type(duckdb_prepared_statement prepared_statement,

idx_t param_idx);

duckdb_state duckdb_clear_bindings(duckdb_prepared_statement prepared_

statement);

duckdb_state duckdb_bind_value(duckdb_prepared_statement prepared_

statement, idx_t param_idx, duckdb_value val);

duckdb_state duckdb_bind_parameter_index(duckdb_prepared_statement

prepared_statement, idx_t *param_idx_out, const char *name);

duckdb_state duckdb_bind_boolean(duckdb_prepared_statement prepared_

statement, idx_t param_idx, bool val);

duckdb_state duckdb_bind_int8(duckdb_prepared_statement prepared_statement,

idx_t param_idx, int8_t val);

duckdb_state duckdb_bind_int16(duckdb_prepared_statement prepared_

statement, idx_t param_idx, int16_t val);

duckdb_state duckdb_bind_int32(duckdb_prepared_statement prepared_

statement, idx_t param_idx, int32_t val);

duckdb_state duckdb_bind_int64(duckdb_prepared_statement prepared_

statement, idx_t param_idx, int64_t val);

duckdb_state duckdb_bind_hugeint(duckdb_prepared_statement prepared_

statement, idx_t param_idx, duckdb_hugeint val);

duckdb_state duckdb_bind_decimal(duckdb_prepared_statement prepared_

statement, idx_t param_idx, duckdb_decimal val);

duckdb_state duckdb_bind_uint8(duckdb_prepared_statement prepared_

statement, idx_t param_idx, uint8_t val);

111

DuckDB Documentation

duckdb_state duckdb_bind_uint16(duckdb_prepared_statement prepared_

statement, idx_t param_idx, uint16_t val);

duckdb_state duckdb_bind_uint32(duckdb_prepared_statement prepared_

statement, idx_t param_idx, uint32_t val);

duckdb_state duckdb_bind_uint64(duckdb_prepared_statement prepared_

statement, idx_t param_idx, uint64_t val);

duckdb_state duckdb_bind_float(duckdb_prepared_statement prepared_

statement, idx_t param_idx, float val);

duckdb_state duckdb_bind_double(duckdb_prepared_statement prepared_

statement, idx_t param_idx, double val);

duckdb_state duckdb_bind_date(duckdb_prepared_statement prepared_statement,

idx_t param_idx, duckdb_date val);

duckdb_state duckdb_bind_time(duckdb_prepared_statement prepared_statement,

idx_t param_idx, duckdb_time val);

duckdb_state duckdb_bind_timestamp(duckdb_prepared_statement prepared_

statement, idx_t param_idx, duckdb_timestamp val);

duckdb_state duckdb_bind_interval(duckdb_prepared_statement prepared_

statement, idx_t param_idx, duckdb_interval val);

duckdb_state duckdb_bind_varchar(duckdb_prepared_statement prepared_

statement, idx_t param_idx, const char *val);

duckdb_state duckdb_bind_varchar_length(duckdb_prepared_statement prepared_

statement, idx_t param_idx, const char *val, idx_t length);

duckdb_state duckdb_bind_blob(duckdb_prepared_statement prepared_statement,

idx_t param_idx, const void *data, idx_t length);

duckdb_state duckdb_bind_null(duckdb_prepared_statement prepared_statement,

idx_t param_idx);

duckdb_state duckdb_execute_prepared(duckdb_prepared_statement prepared_

statement, duckdb_result *out_result);

duckdb_state duckdb_execute_prepared_arrow(duckdb_prepared_statement

prepared_statement, duckdb_arrow *out_result);

duckdb_state duckdb_arrow_scan(duckdb_connection connection, const char

*table_name, duckdb_arrow_stream arrow);

duckdb_state duckdb_arrow_array_scan(duckdb_connection connection, const

char *table_name, duckdb_arrow_schema arrow_schema, duckdb_arrow_array

arrow_array, duckdb_arrow_stream *out_stream);



duckdb_prepare Create a prepared statement object from a query.

Note that aer calling duckdb_prepare, the prepared statement should always be destroyed using

duckdb_destroy_prepare, even if the prepare fails.

If the prepare fails, duckdb_prepare_error can be called to obtain the reason why the prepare

failed.

112

DuckDB Documentation

Syntax

duckdb_state duckdb_prepare(

duckdb_connection connection,

const char *query,

duckdb_prepared_statement *out_prepared_statement

);

Parameters

• connection

The connection object

• query

The SQL query to prepare

• out_prepared_statement

The resulting prepared statement object

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_destroy_prepare Closes the prepared statement and de‑allocates all memory allo‑

cated for the statement.

Syntax

void duckdb_destroy_prepare(

duckdb_prepared_statement *prepared_statement

);

Parameters

• prepared_statement

The prepared statement to destroy.

113

DuckDB Documentation

duckdb_prepare_error Returns the error message associated with the given prepared state‑

ment. If the prepared statement has no error message, this returns nullptr instead.

The error message should not be freed. It will be de‑allocated when duckdb_destroy_prepare

is called.

Syntax

const char *duckdb_prepare_error(

duckdb_prepared_statement prepared_statement

);

Parameters

• prepared_statement

The prepared statement to obtain the error from.

• returns

The error message, or nullptr if there is none.

duckdb_nparams Returns the number of parameters that can be provided to the given prepared

statement.

Returns 0 if the query was not successfully prepared.

Syntax

idx_t duckdb_nparams(

duckdb_prepared_statement prepared_statement

);

Parameters

• prepared_statement

The prepared statement to obtain the number of parameters for.

duckdb_parameter_name Returns the name used to identify the parameter The returned string

should be freed using duckdb_free.

Returns NULL if the index is out of range for the provided prepared statement.

114

DuckDB Documentation

Syntax

const char *duckdb_parameter_name(

duckdb_prepared_statement prepared_statement,

idx_t index

);

Parameters

• prepared_statement

The prepared statement for which to get the parameter name from.

duckdb_param_type Returns the parameter type for the parameter at the given index.

Returns DUCKDB_TYPE_INVALID if the parameter index is out of range or the statement was not

successfully prepared.

Syntax

duckdb_type duckdb_param_type(

duckdb_prepared_statement prepared_statement,

idx_t param_idx

);

Parameters

• prepared_statement

The prepared statement.

• param_idx

The parameter index.

• returns

The parameter type

duckdb_clear_bindings Clear the params bind to the prepared statement.

115

DuckDB Documentation

Syntax

duckdb_state duckdb_clear_bindings(

duckdb_prepared_statement prepared_statement

);

duckdb_bind_value Binds a value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_value(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

duckdb_value val

);

duckdb_bind_parameter_index Retrieve the index of the parameter for the prepared state‑

ment, identified by name

Syntax

duckdb_state duckdb_bind_parameter_index(

duckdb_prepared_statement prepared_statement,

idx_t *param_idx_out,

const char *name

);

duckdb_bind_boolean Binds a bool value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_boolean(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

bool val

);

duckdb_bind_int8 Binds an int8_t value to the prepared statement at the specified index.

116

DuckDB Documentation

Syntax

duckdb_state duckdb_bind_int8(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

int8_t val

);

duckdb_bind_int16 Binds an int16_t value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_int16(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

int16_t val

);

duckdb_bind_int32 Binds an int32_t value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_int32(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

int32_t val

);

duckdb_bind_int64 Binds an int64_t value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_int64(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

int64_t val

);

duckdb_bind_hugeint Binds a duckdb_hugeint value to the prepared statement at the speci‑

fied index.

117

DuckDB Documentation

Syntax

duckdb_state duckdb_bind_hugeint(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

duckdb_hugeint val

);

duckdb_bind_decimal Binds a duckdb_decimal value to the prepared statement at the speci‑

fied index.

Syntax

duckdb_state duckdb_bind_decimal(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

duckdb_decimal val

);

duckdb_bind_uint8 Binds an uint8_t value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_uint8(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

uint8_t val

);

duckdb_bind_uint16 Binds an uint16_t value to the prepared statement at the specified in‑

dex.

Syntax

duckdb_state duckdb_bind_uint16(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

uint16_t val

);

118

DuckDB Documentation

duckdb_bind_uint32 Binds an uint32_t value to the prepared statement at the specified in‑

dex.

Syntax

duckdb_state duckdb_bind_uint32(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

uint32_t val

);

duckdb_bind_uint64 Binds an uint64_t value to the prepared statement at the specified in‑

dex.

Syntax

duckdb_state duckdb_bind_uint64(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

uint64_t val

);

duckdb_bind_float Binds a float value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_float(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

float val

);

duckdb_bind_double Binds a double value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_double(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

119

DuckDB Documentation

double val

);

duckdb_bind_date Binds a duckdb_date value to the prepared statement at the specified in‑

dex.

Syntax

duckdb_state duckdb_bind_date(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

duckdb_date val

);

duckdb_bind_time Binds a duckdb_time value to the prepared statement at the specified in‑

dex.

Syntax

duckdb_state duckdb_bind_time(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

duckdb_time val

);

duckdb_bind_timestamp Binds a duckdb_timestamp value to the prepared statement at the

specified index.

Syntax

duckdb_state duckdb_bind_timestamp(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

duckdb_timestamp val

);

duckdb_bind_interval Binds a duckdb_interval value to the prepared statement at the spec‑

ified index.

120

DuckDB Documentation

Syntax

duckdb_state duckdb_bind_interval(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

duckdb_interval val

);

duckdb_bind_varchar Binds a null‑terminated varchar value to the prepared statement at the

specified index.

Syntax

duckdb_state duckdb_bind_varchar(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

const char *val

);

duckdb_bind_varchar_length Binds a varchar value to the prepared statement at the spec‑

ified index.

Syntax

duckdb_state duckdb_bind_varchar_length(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

const char *val,

idx_t length

);

duckdb_bind_blob Binds a blob value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_blob(

duckdb_prepared_statement prepared_statement,

idx_t param_idx,

const void *data,

idx_t length

);

121

DuckDB Documentation

duckdb_bind_null Binds a NULL value to the prepared statement at the specified index.

Syntax

duckdb_state duckdb_bind_null(

duckdb_prepared_statement prepared_statement,

idx_t param_idx

);

duckdb_execute_prepared Executes the prepared statement with the given bound parame‑

ters, and returns a materialized query result.

This method can be called multiple times for each prepared statement, and the parameters can be

modified between calls to this function.

Syntax

duckdb_state duckdb_execute_prepared(

duckdb_prepared_statement prepared_statement,

duckdb_result *out_result

);

Parameters

• prepared_statement

The prepared statement to execute.

• out_result

The query result.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_execute_prepared_arrow Executes the prepared statement with the given bound

parameters, and returns an arrow query result.

122

DuckDB Documentation

Syntax

duckdb_state duckdb_execute_prepared_arrow(

duckdb_prepared_statement prepared_statement,

duckdb_arrow *out_result

);

Parameters

• prepared_statement

The prepared statement to execute.

• out_result

The query result.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_arrow_scan Scans the Arrow stream and creates a view with the given name.

Syntax

duckdb_state duckdb_arrow_scan(

duckdb_connection connection,

const char *table_name,

duckdb_arrow_stream arrow

);

Parameters

• connection

The connection on which to execute the scan.

• table_name

Name of the temporary view to create.

• arrow

Arrow stream wrapper.

123

DuckDB Documentation

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_arrow_array_scan Scans the Arrow array and creates a view with the given name.

Syntax

duckdb_state duckdb_arrow_array_scan(

duckdb_connection connection,

const char *table_name,

duckdb_arrow_schema arrow_schema,

duckdb_arrow_array arrow_array,

duckdb_arrow_stream *out_stream

);

Parameters

• connection

The connection on which to execute the scan.

• table_name

Name of the temporary view to create.

• arrow_schema

Arrow schema wrapper.

• arrow_array

Arrow array wrapper.

• out_stream

Output array stream that wraps around the passed schema, for releasing/deleting once done.

• returns

DuckDBSuccess on success or DuckDBError on failure.

124

DuckDB Documentation

C API ‑ Appender

Appenders are the most eicient way of loading data into DuckDB from within the C interface, and are

recommended for fast data loading. The appender is much faster than using prepared statements or

individual INSERT INTO statements.

Appends are made in row‑wise format. For every column, a duckdb_append_[type] call should

be made, aer which the row should be finished by calling duckdb_appender_end_row. Aer all

rows have been appended, duckdb_appender_destroy should be used to finalize the appender

and clean up the resulting memory.

Note that duckdb_appender_destroy should always be called on the resulting appender, even

if the function returns DuckDBError.

Example

duckdb_query(con, "CREATE TABLE people(id INTEGER, name VARCHAR)", NULL);

duckdb_appender appender;

if (duckdb_appender_create(con, NULL, "people", &appender) == DuckDBError) {

// handle error

}

// append the first row (1, Mark)

duckdb_append_int32(appender, 1);

duckdb_append_varchar(appender, "Mark");

duckdb_appender_end_row(appender);

// append the second row (2, Hannes)

duckdb_append_int32(appender, 2);

duckdb_append_varchar(appender, "Hannes");

duckdb_appender_end_row(appender);

// finish appending and flush all the rows to the table

duckdb_appender_destroy(&appender);

API Reference

duckdb_state duckdb_appender_create(duckdb_connection connection, const char

*schema, const char *table, duckdb_appender *out_appender);

const char *duckdb_appender_error(duckdb_appender appender);

duckdb_state duckdb_appender_flush(duckdb_appender appender);

125

DuckDB Documentation

duckdb_state duckdb_appender_close(duckdb_appender appender);

duckdb_state duckdb_appender_destroy(duckdb_appender *appender);

duckdb_state duckdb_appender_begin_row(duckdb_appender appender);

duckdb_state duckdb_appender_end_row(duckdb_appender appender);

duckdb_state duckdb_append_bool(duckdb_appender appender, bool value);

duckdb_state duckdb_append_int8(duckdb_appender appender, int8_t value);

duckdb_state duckdb_append_int16(duckdb_appender appender, int16_t value);

duckdb_state duckdb_append_int32(duckdb_appender appender, int32_t value);

duckdb_state duckdb_append_int64(duckdb_appender appender, int64_t value);

duckdb_state duckdb_append_hugeint(duckdb_appender appender, duckdb_hugeint

value);

duckdb_state duckdb_append_uint8(duckdb_appender appender, uint8_t value);

duckdb_state duckdb_append_uint16(duckdb_appender appender, uint16_t value);

duckdb_state duckdb_append_uint32(duckdb_appender appender, uint32_t value);

duckdb_state duckdb_append_uint64(duckdb_appender appender, uint64_t value);

duckdb_state duckdb_append_float(duckdb_appender appender, float value);

duckdb_state duckdb_append_double(duckdb_appender appender, double value);

duckdb_state duckdb_append_date(duckdb_appender appender, duckdb_date

value);

duckdb_state duckdb_append_time(duckdb_appender appender, duckdb_time

value);

duckdb_state duckdb_append_timestamp(duckdb_appender appender, duckdb_

timestamp value);

duckdb_state duckdb_append_interval(duckdb_appender appender, duckdb_

interval value);

duckdb_state duckdb_append_varchar(duckdb_appender appender, const char

*val);

duckdb_state duckdb_append_varchar_length(duckdb_appender appender, const

char *val, idx_t length);

duckdb_state duckdb_append_blob(duckdb_appender appender, const void *data,

idx_t length);

duckdb_state duckdb_append_null(duckdb_appender appender);

duckdb_state duckdb_append_data_chunk(duckdb_appender appender, duckdb_data_

chunk chunk);

duckdb_appender_create Creates an appender object.

Syntax

duckdb_state duckdb_appender_create(

duckdb_connection connection,

const char *schema,

126

DuckDB Documentation

const char *table,

duckdb_appender *out_appender

);

Parameters

• connection

The connection context to create the appender in.

• schema

The schema of the table to append to, or nullptr for the default schema.

• table

The table name to append to.

• out_appender

The resulting appender object.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_appender_error Returns the error message associatedwith the given appender. If the

appender has no error message, this returns nullptr instead.

The error message should not be freed. It will be de‑allocated when duckdb_appender_destroy

is called.

Syntax

const char *duckdb_appender_error(

duckdb_appender appender

);

Parameters

• appender

The appender to get the error from.

• returns

The error message, or nullptr if there is none.

127

DuckDB Documentation

duckdb_appender_flush Flush the appender to the table, forcing the cache of the appender

to be cleared and the data to be appended to the base table.

This should generally not be used unless you know what you are doing. Instead, call duckdb_

appender_destroy when you are done with the appender.

Syntax

duckdb_state duckdb_appender_flush(

duckdb_appender appender

);

Parameters

• appender

The appender to flush.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_appender_close Close the appender, flushing all intermediate state in the appender

to the table and closing it for further appends.

This is generally not necessary. Call duckdb_appender_destroy instead.

Syntax

duckdb_state duckdb_appender_close(

duckdb_appender appender

);

Parameters

• appender

The appender to flush and close.

• returns

DuckDBSuccess on success or DuckDBError on failure.

128

DuckDB Documentation

duckdb_appender_destroy Close the appender and destroy it. Flushing all intermediate state

in the appender to the table, and de‑allocating all memory associated with the appender.

Syntax

duckdb_state duckdb_appender_destroy(

duckdb_appender *appender

);

Parameters

• appender

The appender to flush, close and destroy.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_appender_begin_row A nop function, provided for backwards compatibility reasons.

Does nothing. Only duckdb_appender_end_row is required.

Syntax

duckdb_state duckdb_appender_begin_row(

duckdb_appender appender

);

duckdb_appender_end_row Finish the current row of appends. Aer end_row is called, the

next row can be appended.

Syntax

duckdb_state duckdb_appender_end_row(

duckdb_appender appender

);

129

DuckDB Documentation

Parameters

• appender

The appender.

• returns

DuckDBSuccess on success or DuckDBError on failure.

duckdb_append_bool Append a bool value to the appender.

Syntax

duckdb_state duckdb_append_bool(

duckdb_appender appender,

bool value

);

duckdb_append_int8 Append an int8_t value to the appender.

Syntax

duckdb_state duckdb_append_int8(

duckdb_appender appender,

int8_t value

);

duckdb_append_int16 Append an int16_t value to the appender.

Syntax

duckdb_state duckdb_append_int16(

duckdb_appender appender,

int16_t value

);

duckdb_append_int32 Append an int32_t value to the appender.

130

DuckDB Documentation

Syntax

duckdb_state duckdb_append_int32(

duckdb_appender appender,

int32_t value

);

duckdb_append_int64 Append an int64_t value to the appender.

Syntax

duckdb_state duckdb_append_int64(

duckdb_appender appender,

int64_t value

);

duckdb_append_hugeint Append a duckdb_hugeint value to the appender.

Syntax

duckdb_state duckdb_append_hugeint(

duckdb_appender appender,

duckdb_hugeint value

);

duckdb_append_uint8 Append a uint8_t value to the appender.

Syntax

duckdb_state duckdb_append_uint8(

duckdb_appender appender,

uint8_t value

);

duckdb_append_uint16 Append a uint16_t value to the appender.

131

DuckDB Documentation

Syntax

duckdb_state duckdb_append_uint16(

duckdb_appender appender,

uint16_t value

);

duckdb_append_uint32 Append a uint32_t value to the appender.

Syntax

duckdb_state duckdb_append_uint32(

duckdb_appender appender,

uint32_t value

);

duckdb_append_uint64 Append a uint64_t value to the appender.

Syntax

duckdb_state duckdb_append_uint64(

duckdb_appender appender,

uint64_t value

);

duckdb_append_float Append a float value to the appender.

Syntax

duckdb_state duckdb_append_float(

duckdb_appender appender,

float value

);

duckdb_append_double Append a double value to the appender.

132

DuckDB Documentation

Syntax

duckdb_state duckdb_append_double(

duckdb_appender appender,

double value

);

duckdb_append_date Append a duckdb_date value to the appender.

Syntax

duckdb_state duckdb_append_date(

duckdb_appender appender,

duckdb_date value

);

duckdb_append_time Append a duckdb_time value to the appender.

Syntax

duckdb_state duckdb_append_time(

duckdb_appender appender,

duckdb_time value

);

duckdb_append_timestamp Append a duckdb_timestamp value to the appender.

Syntax

duckdb_state duckdb_append_timestamp(

duckdb_appender appender,

duckdb_timestamp value

);

duckdb_append_interval Append a duckdb_interval value to the appender.

133

DuckDB Documentation

Syntax

duckdb_state duckdb_append_interval(

duckdb_appender appender,

duckdb_interval value

);

duckdb_append_varchar Append a varchar value to the appender.

Syntax

duckdb_state duckdb_append_varchar(

duckdb_appender appender,

const char *val

);

duckdb_append_varchar_length Append a varchar value to the appender.

Syntax

duckdb_state duckdb_append_varchar_length(

duckdb_appender appender,

const char *val,

idx_t length

);

duckdb_append_blob Append a blob value to the appender.

Syntax

duckdb_state duckdb_append_blob(

duckdb_appender appender,

const void *data,

idx_t length

);

duckdb_append_null Append a NULL value to the appender (of any type).

134

DuckDB Documentation

Syntax

duckdb_state duckdb_append_null(

duckdb_appender appender

);

duckdb_append_data_chunk Appends a pre‑filled data chunk to the specified appender.

The types of the data chunk must exactly match the types of the table, no casting is performed. If the

types do not match or the appender is in an invalid state, DuckDBError is returned. If the append is

successful, DuckDBSuccess is returned.

Syntax

duckdb_state duckdb_append_data_chunk(

duckdb_appender appender,

duckdb_data_chunk chunk

);

Parameters

• appender

The appender to append to.

• chunk

The data chunk to append.

• returns

The return state.

C API ‑ Table Functions

The table function API can be used to define a table function that can then be called from within

DuckDB in the FROM clause of a query.

API Reference

duckdb_table_function duckdb_create_table_function();

void duckdb_destroy_table_function(duckdb_table_function *table_function);

135

DuckDB Documentation

void duckdb_table_function_set_name(duckdb_table_function table_function,

const char *name);

void duckdb_table_function_add_parameter(duckdb_table_function table_

function, duckdb_logical_type type);

void duckdb_table_function_add_named_parameter(duckdb_table_function table_

function, const char *name, duckdb_logical_type type);

void duckdb_table_function_set_extra_info(duckdb_table_function table_

function, void *extra_info, duckdb_delete_callback_t destroy);

void duckdb_table_function_set_bind(duckdb_table_function table_function,

duckdb_table_function_bind_t bind);

void duckdb_table_function_set_init(duckdb_table_function table_function,

duckdb_table_function_init_t init);

void duckdb_table_function_set_local_init(duckdb_table_function table_

function, duckdb_table_function_init_t init);

void duckdb_table_function_set_function(duckdb_table_function table_

function, duckdb_table_function_t function);

void duckdb_table_function_supports_projection_pushdown(duckdb_table_

function table_function, bool pushdown);

duckdb_state duckdb_register_table_function(duckdb_connection con, duckdb_

table_function function);

Table Function Bind

void *duckdb_bind_get_extra_info(duckdb_bind_info info);

void duckdb_bind_add_result_column(duckdb_bind_info info, const char *name,

duckdb_logical_type type);

idx_t duckdb_bind_get_parameter_count(duckdb_bind_info info);

duckdb_value duckdb_bind_get_parameter(duckdb_bind_info info, idx_t index);

duckdb_value duckdb_bind_get_named_parameter(duckdb_bind_info info, const

char *name);

void duckdb_bind_set_bind_data(duckdb_bind_info info, void *bind_data,

duckdb_delete_callback_t destroy);

void duckdb_bind_set_cardinality(duckdb_bind_info info, idx_t cardinality,

bool is_exact);

void duckdb_bind_set_error(duckdb_bind_info info, const char *error);

Table Function Init

void *duckdb_init_get_extra_info(duckdb_init_info info);

void *duckdb_init_get_bind_data(duckdb_init_info info);

void duckdb_init_set_init_data(duckdb_init_info info, void *init_data,

duckdb_delete_callback_t destroy);

idx_t duckdb_init_get_column_count(duckdb_init_info info);

136

DuckDB Documentation

idx_t duckdb_init_get_column_index(duckdb_init_info info, idx_t column_

index);

void duckdb_init_set_max_threads(duckdb_init_info info, idx_t max_threads);

void duckdb_init_set_error(duckdb_init_info info, const char *error);

Table Function

void *duckdb_function_get_extra_info(duckdb_function_info info);

void *duckdb_function_get_bind_data(duckdb_function_info info);

void *duckdb_function_get_init_data(duckdb_function_info info);

void *duckdb_function_get_local_init_data(duckdb_function_info info);

void duckdb_function_set_error(duckdb_function_info info, const char

*error);

duckdb_create_table_function Creates a new empty table function.

The return value should be destroyed with duckdb_destroy_table_function.

Syntax

duckdb_table_function duckdb_create_table_function(

);

Parameters

• returns

The table function object.

duckdb_destroy_table_function Destroys the given table function object.

Syntax

void duckdb_destroy_table_function(

duckdb_table_function *table_function

);

Parameters

• table_function

The table function to destroy

137

DuckDB Documentation

duckdb_table_function_set_name Sets the name of the given table function.

Syntax

void duckdb_table_function_set_name(

duckdb_table_function table_function,

const char *name

);

Parameters

• table_function

The table function

• name

The name of the table function

duckdb_table_function_add_parameter Adds a parameter to the table function.

Syntax

void duckdb_table_function_add_parameter(

duckdb_table_function table_function,

duckdb_logical_type type

);

Parameters

• table_function

The table function

• type

The type of the parameter to add.

duckdb_table_function_add_named_parameter Adds a named parameter to the table

function.

138

DuckDB Documentation

Syntax

void duckdb_table_function_add_named_parameter(

duckdb_table_function table_function,

const char *name,

duckdb_logical_type type

);

Parameters

• table_function

The table function

• name

The name of the parameter

• type

The type of the parameter to add.

duckdb_table_function_set_extra_info Assigns extra information to the table function

that can be fetched during binding, etc.

Syntax

void duckdb_table_function_set_extra_info(

duckdb_table_function table_function,

void *extra_info,

duckdb_delete_callback_t destroy

);

Parameters

• table_function

The table function

• extra_info

The extra information

• destroy

The callback that will be called to destroy the bind data (if any)

139

DuckDB Documentation

duckdb_table_function_set_bind Sets the bind function of the table function

Syntax

void duckdb_table_function_set_bind(

duckdb_table_function table_function,

duckdb_table_function_bind_t bind

);

Parameters

• table_function

The table function

• bind

The bind function

duckdb_table_function_set_init Sets the init function of the table function

Syntax

void duckdb_table_function_set_init(

duckdb_table_function table_function,

duckdb_table_function_init_t init

);

Parameters

• table_function

The table function

• init

The init function

duckdb_table_function_set_local_init Sets the thread‑local init function of the table

function

140

DuckDB Documentation

Syntax

void duckdb_table_function_set_local_init(

duckdb_table_function table_function,

duckdb_table_function_init_t init

);

Parameters

• table_function

The table function

• init

The init function

duckdb_table_function_set_function Sets the main function of the table function

Syntax

void duckdb_table_function_set_function(

duckdb_table_function table_function,

duckdb_table_function_t function

);

Parameters

• table_function

The table function

• function

The function

duckdb_table_function_supports_projection_pushdown Sets whether or not the

given table function supports projection pushdown.

If this is set to true, the system will provide a list of all required columns in the init stage through

the duckdb_init_get_column_count and duckdb_init_get_column_index functions.

If this is set to false (the default), the system will expect all columns to be projected.

141

DuckDB Documentation

Syntax

void duckdb_table_function_supports_projection_pushdown(

duckdb_table_function table_function,

bool pushdown

);

Parameters

• table_function

The table function

• pushdown

True if the table function supports projection pushdown, false otherwise.

duckdb_register_table_function Register the table function object within the given con‑

nection.

The function requires at least a name, a bind function, an init function and a main function.

If the function is incomplete or a function with this name already exists DuckDBError is returned.

Syntax

duckdb_state duckdb_register_table_function(

duckdb_connection con,

duckdb_table_function function

);

Parameters

• con

The connection to register it in.

• function

The function pointer

• returns

Whether or not the registration was successful.

142

DuckDB Documentation

duckdb_bind_get_extra_info Retrieves the extra info of the function as set in duckdb_

table_function_set_extra_info

Syntax

void *duckdb_bind_get_extra_info(

duckdb_bind_info info

);

Parameters

• info

The info object

• returns

The extra info

duckdb_bind_add_result_column Adds a result column to the output of the table func‑

tion.

Syntax

void duckdb_bind_add_result_column(

duckdb_bind_info info,

const char *name,

duckdb_logical_type type

);

Parameters

• info

The info object

• name

The name of the column

• type

The logical type of the column

143

DuckDB Documentation

duckdb_bind_get_parameter_count Retrieves the number of regular (non‑named) param‑

eters to the function.

Syntax

idx_t duckdb_bind_get_parameter_count(

duckdb_bind_info info

);

Parameters

• info

The info object

• returns

The number of parameters

duckdb_bind_get_parameter Retrieves the parameter at the given index.

The result must be destroyed with duckdb_destroy_value.

Syntax

duckdb_value duckdb_bind_get_parameter(

duckdb_bind_info info,

idx_t index

);

Parameters

• info

The info object

• index

The index of the parameter to get

• returns

The value of the parameter. Must be destroyed with duckdb_destroy_value.

144

DuckDB Documentation

duckdb_bind_get_named_parameter Retrieves a named parameter with the given name.

The result must be destroyed with duckdb_destroy_value.

Syntax

duckdb_value duckdb_bind_get_named_parameter(

duckdb_bind_info info,

const char *name

);

Parameters

• info

The info object

• name

The name of the parameter

• returns

The value of the parameter. Must be destroyed with duckdb_destroy_value.

duckdb_bind_set_bind_data Setsthe user‑provided bind datain the bind object. This object

can be retrieved again during execution.

Syntax

void duckdb_bind_set_bind_data(

duckdb_bind_info info,

void *bind_data,

duckdb_delete_callback_t destroy

);

Parameters

• info

The info object

• extra_data

145

DuckDB Documentation

The bind data object.

• destroy

The callback that will be called to destroy the bind data (if any)

duckdb_bind_set_cardinality Sets the cardinality estimate for the table function, used for

optimization.

Syntax

void duckdb_bind_set_cardinality(

duckdb_bind_info info,

idx_t cardinality,

bool is_exact

);

Parameters

• info

The bind data object.

• is_exact

Whether or not the cardinality estimate is exact, or an approximation

duckdb_bind_set_error Report that an error has occurred while calling bind.

Syntax

void duckdb_bind_set_error(

duckdb_bind_info info,

const char *error

);

Parameters

• info

The info object

• error

The error message

146

DuckDB Documentation

duckdb_init_get_extra_info Retrieves the extra info of the function as set in duckdb_

table_function_set_extra_info

Syntax

void *duckdb_init_get_extra_info(

duckdb_init_info info

);

Parameters

• info

The info object

• returns

The extra info

duckdb_init_get_bind_data Gets the bind data set by duckdb_bind_set_bind_data

during the bind.

Note that the bind data should be considered as read‑only. For tracking state, use the init data in‑

stead.

Syntax

void *duckdb_init_get_bind_data(

duckdb_init_info info

);

Parameters

• info

The info object

• returns

The bind data object

duckdb_init_set_init_data Sets the user‑provided init data in the init object. This object

can be retrieved again during execution.

147

DuckDB Documentation

Syntax

void duckdb_init_set_init_data(

duckdb_init_info info,

void *init_data,

duckdb_delete_callback_t destroy

);

Parameters

• info

The info object

• extra_data

The init data object.

• destroy

The callback that will be called to destroy the init data (if any)

duckdb_init_get_column_count Returns the number of projected columns.

This function must be used if projection pushdown is enabled to figure out which columns to emit.

Syntax

idx_t duckdb_init_get_column_count(

duckdb_init_info info

);

Parameters

• info

The info object

• returns

The number of projected columns.

duckdb_init_get_column_index Returns the column index of the projected column at the

specified position.

This function must be used if projection pushdown is enabled to figure out which columns to emit.

148

DuckDB Documentation

Syntax

idx_t duckdb_init_get_column_index(

duckdb_init_info info,

idx_t column_index

);

Parameters

• info

The info object

• column_index

The index at which to getthe projectedcolumn index, from 0..duckdb_init_get_column_count(info)

• returns

The column index of the projected column.

duckdb_init_set_max_threads Sets how many threads can process this table function in

parallel (default: 1)

Syntax

void duckdb_init_set_max_threads(

duckdb_init_info info,

idx_t max_threads

);

Parameters

• info

The info object

• max_threads

The maximum amount of threads that can process this table function

duckdb_init_set_error Report that an error has occurred while calling init.

149

DuckDB Documentation

Syntax

void duckdb_init_set_error(

duckdb_init_info info,

const char *error

);

Parameters

• info

The info object

• error

The error message

duckdb_function_get_extra_info Retrieves the extra info of the function as set in

duckdb_table_function_set_extra_info

Syntax

void *duckdb_function_get_extra_info(

duckdb_function_info info

);

Parameters

• info

The info object

• returns

The extra info

duckdb_function_get_bind_data Gets the bind data set by duckdb_bind_set_bind_

data during the bind.

Note that the bind data should be considered as read‑only. For tracking state, use the init data in‑

stead.

150

DuckDB Documentation

Syntax

void *duckdb_function_get_bind_data(

duckdb_function_info info

);

Parameters

• info

The info object

• returns

The bind data object

duckdb_function_get_init_data Gets the init data set by duckdb_init_set_init_

data during the init.

Syntax

void *duckdb_function_get_init_data(

duckdb_function_info info

);

Parameters

• info

The info object

• returns

The init data object

duckdb_function_get_local_init_data Gets the thread‑local init data set by duckdb_

init_set_init_data during the local_init.

Syntax

void *duckdb_function_get_local_init_data(

duckdb_function_info info

);

151

DuckDB Documentation

Parameters

• info

The info object

• returns

The init data object

duckdb_function_set_error Report that an error has occurred while executing the

function.

Syntax

void duckdb_function_set_error(

duckdb_function_info info,

const char *error

);

Parameters

• info

The info object

• error

The error message

C API ‑ Replacement Scans

The replacement scan API can be used to register a callback that is called when a table is read that

does not exist in the catalog. For example, when a query such as SELECT * FROM my_table

is executed and my_table does not exist, the replacement scan callback will be called with my_

tableas parameter. The replacement scan can then insert a table function with a specific parameter

to replace the read of the table.

152

DuckDB Documentation

API Reference

void duckdb_add_replacement_scan(duckdb_database db, duckdb_replacement_

callback_t replacement, void *extra_data, duckdb_delete_callback_t

delete_callback);



void duckdb_replacement_scan_set_function_name(duckdb_replacement_scan_info

info, const char *function_name);

void duckdb_replacement_scan_add_parameter(duckdb_replacement_scan_info

info, duckdb_value parameter);

void duckdb_replacement_scan_set_error(duckdb_replacement_scan_info info,

const char *error);

duckdb_add_replacement_scan Add a replacement scan definition to the specified

database

Syntax

void duckdb_add_replacement_scan(

duckdb_database db,

duckdb_replacement_callback_t replacement,

void *extra_data,

duckdb_delete_callback_t delete_callback

);

Parameters

• db

The database object to add the replacement scan to

• replacement

The replacement scan callback

• extra_data

Extra data that is passed back into the specified callback

• delete_callback

The delete callback to call on the extra data, if any

153

DuckDB Documentation

duckdb_replacement_scan_set_function_name Sets the replacement function name to

use. If this function is called in the replacement callback, the replacement scan is performed. If it is

not called, the replacement callback is not performed.

Syntax

void duckdb_replacement_scan_set_function_name(

duckdb_replacement_scan_info info,

const char *function_name

);

Parameters

• info

The info object

• function_name

The function name to substitute.

duckdb_replacement_scan_add_parameter Adds a parameter to the replacement scan

function.

Syntax

void duckdb_replacement_scan_add_parameter(

duckdb_replacement_scan_info info,

duckdb_value parameter

);

Parameters

• info

The info object

• parameter

The parameter to add.

duckdb_replacement_scan_set_error Report that an error has occurred while executing

the replacement scan.

154

DuckDB Documentation

Syntax

void duckdb_replacement_scan_set_error(

duckdb_replacement_scan_info info,

const char *error

);

Parameters

• info

The info object

• error

The error message

C API ‑ Complete API

API Reference

Open/Connect

duckdb_state duckdb_open(const char *path, duckdb_database *out_database);

duckdb_state duckdb_open_ext(const char *path, duckdb_database *out_

database, duckdb_config config, char **out_error);

void duckdb_close(duckdb_database *database);

duckdb_state duckdb_connect(duckdb_database database, duckdb_connection

*out_connection);

void duckdb_interrupt(duckdb_connection connection);

double duckdb_query_progress(duckdb_connection connection);

void duckdb_disconnect(duckdb_connection *connection);

const char *duckdb_library_version();

Configuration

duckdb_state duckdb_create_config(duckdb_config *out_config);

size_t duckdb_config_count();

duckdb_state duckdb_get_config_flag(size_t index, const char **out_name,

const char **out_description);

duckdb_state duckdb_set_config(duckdb_config config, const char *name, const

char *option);

void duckdb_destroy_config(duckdb_config *config);

155

DuckDB Documentation

Query Execution

duckdb_state duckdb_query(duckdb_connection connection, const char *query,

duckdb_result *out_result);

void duckdb_destroy_result(duckdb_result *result);

const char *duckdb_column_name(duckdb_result *result, idx_t col);

duckdb_type duckdb_column_type(duckdb_result *result, idx_t col);

duckdb_logical_type duckdb_column_logical_type(duckdb_result *result, idx_t

col);

idx_t duckdb_column_count(duckdb_result *result);

idx_t duckdb_row_count(duckdb_result *result);

idx_t duckdb_rows_changed(duckdb_result *result);

void *duckdb_column_data(duckdb_result *result, idx_t col);

bool *duckdb_nullmask_data(duckdb_result *result, idx_t col);

const char *duckdb_result_error(duckdb_result *result);

Result Functions