Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

Pragmas

The `PRAGMA` statement is a SQL extension adopted by DuckDB from SQLite. `PRAGMA` statements can be issued in a similar manner to regular SQL statements. `PRAGMA` commands may alter the internal state of the database engine, and can influence the subsequent execution or behavior of the engine.

`PRAGMA` statements that assign a value to an option can also be issued using the `SET` statement and the value of an option can be retrieved using `SELECT current_setting(option_name)`.

For DuckDB's built in configuration options, see the Configuration Reference. DuckDB extensions may register additional configuration options. These are documented in the respective extensions' documentation pages.

This page contains the supported `PRAGMA` settings.

## Metadata

#### Schema Information

List all databases:

    PRAGMA database_list;

List all tables:

    PRAGMA show_tables;

List all tables, with extra information, similarly to `DESCRIBE`:

    PRAGMA show_tables_expanded;

To list all functions:

    PRAGMA functions;

#### Table Information

Get info for a specific table:

    PRAGMA table_info('table_name');
    CALL pragma_table_info('table_name');

`table_info` returns information about the columns of the table with name `table_name`. The exact format of the table returned is given below:

    cid INTEGER,        -- cid of the column
    name VARCHAR,       -- name of the column
    type VARCHAR,       -- type of the column
    notnull BOOLEAN,    -- if the column is marked as NOT NULL
    dflt_value VARCHAR, -- default value of the column, or NULL if not specified
    pk BOOLEAN          -- part of the primary key or not

#### Database Size

Get the file and memory size of each database:

    SET database_size;
    CALL pragma_database_size();

`database_size` returns information about the file and memory size of each database. The column types of the returned results are given below:

    database_name VARCHAR, -- database name
    database_size VARCHAR, -- total block count times the block size
    block_size BIGINT,     -- database block size
    total_blocks BIGINT,   -- total blocks in the database
    used_blocks BIGINT,    -- used blocks in the database
    free_blocks BIGINT,    -- free blocks in the database
    wal_size VARCHAR,      -- write ahead log size
    memory_usage VARCHAR,  -- memory used by the database buffer manager
    memory_limit VARCHAR   -- maximum memory allowed for the database

#### Storage Information

To get storage information:

    PRAGMA storage_info('table_name');
    CALL pragma_storage_info('table_name');

This call returns the following information for the given table:

Name | Type | Description
--- | --- | ---
`row_group_id` | `BIGINT` |
`column_name` | `VARCHAR` |
`column_id` | `BIGINT` |
`column_path` | `VARCHAR` |
`segment_id` | `BIGINT` |
`segment_type` | `VARCHAR` |
`start` | `BIGINT` | The start row id of this chunk
`count` | `BIGINT` | The amount of entries in this storage chunk
`compression` | `VARCHAR` | Compression type used for this column – see the “Lightweight Compression in DuckDB” blog post
`stats` | `VARCHAR` |
`has_updates` | `BOOLEAN` |
`persistent` | `BOOLEAN` | `false` if temporary table
`block_id` | `BIGINT` | empty unless persistent
`block_offset` | `BIGINT` | empty unless persistent

See Storage for more information.

#### Show Databases

The following statement is equivalent to the `SHOW DATABASES` statement:

    PRAGMA show_databases;

## Resource Management

#### Memory Limit

Set the memory limit for the buffer manager:

    SET memory_limit = '1GB';

> Warning The specified memory limit is only applied to the buffer manager. For most queries, the buffer manager handles the majority of the data processed. However, certain in-memory data structures such as vectors and query results are allocated outside of the buffer manager. Additionally, aggregate functions with complex state (e.g., `list`, `mode`, `quantile`, `string_agg`, and `approx` functions) use memory outside of the buffer manager. Therefore, the actual memory consumption can be higher than the specified memory limit.

#### Threads

Set the amount of threads for parallel query execution:

    SET threads = 4;

## Collations

List all available collations:

    PRAGMA collations;

Set the default collation to one of the available ones:

    SET default_collation = 'nocase';

## Default Ordering for NULLs

Set the default ordering for NULLs to be either `NULLS_FIRST`, `NULLS_LAST`, `NULLS_FIRST_ON_ASC_LAST_ON_DESC` or `NULLS_LAST_ON_ASC_FIRST_ON_DESC`:

    SET default_null_order = 'NULLS_FIRST';
    SET default_null_order = 'NULLS_LAST_ON_ASC_FIRST_ON_DESC';

Set the default result set ordering direction to `ASCENDING` or `DESCENDING`:

    SET default_order = 'ASCENDING';
    SET default_order = 'DESCENDING';

## Ordering by Non-Integer Literals

By default, ordering by non-integer literals is not allowed:

    SELECT 42 ORDER BY 'hello world';

    -- Binder Error: ORDER BY non-integer literal has no effect.

To allow this behavior, use the `order_by_non_integer_literal` option:

    SET order_by_non_integer_literal = true;

## Implicit Casting to `VARCHAR`

Prior to version 0.10.0, DuckDB would automatically allow any type to be implicitly cast to `VARCHAR` during function binding. As a result it was possible to e.g., compute the substring of an integer without using an explicit cast. For version v0.10.0 and later an explicit cast is needed instead. To revert to the old behavior that performs implicit casting, set the `old_implicit_casting` variable to `true`:

    SET old_implicit_casting = true;

## Python: Scan All Dataframes

Prior to version 1.1.0, DuckDB's replacement scan mechanism in Python scanned the global Python namespace. To revert to this old behavior, use the following setting:

    SET python_scan_all_frames = true;

## Information on DuckDB

#### Version

Show DuckDB version:

    PRAGMA version;
    CALL pragma_version();

#### Platform

`platform` returns an identifier for the platform the current DuckDB executable has been compiled for, e.g., `osx_arm64`. The format of this identifier matches the platform name as described in the extension loading explainer:

    PRAGMA platform;
    CALL pragma_platform();

#### User Agent

The following statement returns the user agent information, e.g., `duckdb/v0.10.0(osx_arm64)`:

    PRAGMA user_agent;

#### Metadata Information

The following statement returns information on the metadata store (`block_id`, `total_blocks`, `free_blocks`, and `free_list`):

    PRAGMA metadata_info;

## Progress Bar

Show progress bar when running queries:

    PRAGMA enable_progress_bar;

Or:

    PRAGMA enable_print_progress_bar;

Don't show a progress bar for running queries:

    PRAGMA disable_progress_bar;

Or:

    PRAGMA disable_print_progress_bar;

## EXPLAIN Output

The output of `EXPLAIN` can be configured to show only the physical plan.

The default configuration of `EXPLAIN`:

    SET explain_output = 'physical_only';

To only show the optimized query plan:

    SET explain_output = 'optimized_only';

To show all query plans:

    SET explain_output = 'all';

## Profiling

### Enable Profiling

The following query enables profiling with the default format, `query_tree`. Independent of the format, `enable_profiling` is mandatory to enable profiling.

    PRAGMA enable_profiling;
    PRAGMA enable_profile;

##### Profiling Format

The format of `enable_profiling` can be specified as `query_tree`, `json`, `query_tree_optimizer`, or `no_output`. Each format prints its output to the configured output, except `no_output`.

The default format is `query_tree`. It prints the physical query plan and the metrics of each operator in the tree.

    SET enable_profiling = 'query_tree';

Alternatively, `json` returns the physical query plan as JSON:

    SET enable_profiling = 'json';

To return the physical query plan, including optimizer and planner metrics:

    SET enable_profiling = 'query_tree_optimizer';

Database drivers and other applications can also access profiling information through API calls, in which case users can disable any other output. Even though the parameter reads `no_output`, it is essential to note that this only affects printing to the configurable output. When accessing profiling information through API calls, it is still crucial to enable profiling:

    SET enable_profiling = 'no_output';

### Profiling Output

By default, DuckDB prints profiling information to the standard output. However, if you prefer to write the profiling information to a file, you can use `PRAGMA` `profiling_output` to specify a filepath.

> Warning The file contents will be overwritten for every newly issued query. Hence, the file will only contain the profiling information of the last run query:

    SET profiling_output = '/path/to/file.json';
    SET profile_output = '/path/to/file.json';

### Profiling Mode

By default, a limited amount of profiling information is provided (`standard`).

    SET profiling_mode = 'standard';

For more details, use the detailed profiling mode by setting `profiling_mode` to `detailed`. The output of this mode includes profiling of the planner and optimizer stages.

    SET profiling_mode = 'detailed';

### Custom Metrics

By default, profiling enables all metrics except those activated by detailed profiling.

Using the `custom_profiling_settings` `PRAGMA`, each metric, including those from detailed profiling, can be individually enabled or disabled. This `PRAGMA` accepts a JSON object with metric names as keys and Boolean values to toggle them on or off. Settings specified by this `PRAGMA` override the default behavior.

> Note This only affects the metrics when the `enable_profiling` is set to `json` or `no_output`. The `query_tree` and `query_tree_optimizer` always use a default set of metrics.

In the following example, the `CPU_TIME` metric is disabled. The `EXTRA_INFO`, `OPERATOR_CARDINALITY`, and `OPERATOR_TIMING` metrics are enabled.

    SET custom_profiling_settings = '{"CPU_TIME": "false", "EXTRA_INFO": "true", "OPERATOR_CARDINALITY": "true", "OPERATOR_TIMING": "true"}';

The profiling documentation contains an overview of the available metrics.

### Disable Profiling

To disable profiling:

    PRAGMA disable_profiling;
    PRAGMA disable_profile;

## Query Optimization

#### Optimizer

To disable the query optimizer:

    PRAGMA disable_optimizer;

To enable the query optimizer:

    PRAGMA enable_optimizer;

#### Selectively Disabling Optimizers

The `disabled_optimizers` option allows selectively disabling optimization steps. For example, to disable `filter_pushdown` and `statistics_propagation`, run:

    SET disabled_optimizers = 'filter_pushdown,statistics_propagation';

The available optimizations can be queried using the `duckdb_optimizers()` table function.

To re-enable the optimizers, run:

    SET disabled_optimizers = '';

> Warning The `disabled_optimizers` option should only be used for debugging performance issues and should be avoided in production.

## Logging

Set a path for query logging:

    SET log_query_path = '/tmp/duckdb_log/';

Disable query logging:

    SET log_query_path = '';

## Full-Text Search Indexes

The `create_fts_index` and `drop_fts_index` options are only available when the `fts` extension is loaded. Their usage is documented on the Full-Text Search extension page.

## Verification

#### Verification of External Operators

Enable verification of external operators:

    PRAGMA verify_external;

Disable verification of external operators:

    PRAGMA disable_verify_external;

#### Verification of Round-Trip Capabilities

Enable verification of round-trip capabilities for supported logical plans:

    PRAGMA verify_serializer;

Disable verification of round-trip capabilities:

    PRAGMA disable_verify_serializer;

## Object Cache

Enable caching of objects for e.g., Parquet metadata:

    PRAGMA enable_object_cache;

Disable caching of objects:

    PRAGMA disable_object_cache;

## Checkpointing

#### Force Checkpoint

When `CHECKPOINT` is called when no changes are made, force a checkpoint regardless:

    PRAGMA force_checkpoint;

#### Checkpoint on Shutdown

Run a `CHECKPOINT` on successful shutdown and delete the WAL, to leave only a single database file behind:

    PRAGMA enable_checkpoint_on_shutdown;

Don't run a `CHECKPOINT` on shutdown:

    PRAGMA disable_checkpoint_on_shutdown;

## Temp Directory for Spilling Data to Disk

By default, DuckDB uses a temporary directory named `⟨database_file_name⟩.tmp` to spill to disk, located in the same directory as the database file. To change this, use:

    SET temp_directory = '/path/to/temp_dir.tmp/';

## Returning Errors as JSON

The `errors_as_json` option can be set to obtain error information in raw JSON format. For certain errors, extra information or decomposed information is provided for easier machine processing. For example:

    SET errors_as_json = true;

Then, running a query that results in an error produces a JSON output:

    SELECT * FROM nonexistent_tbl;

    {
       "exception_type":"Catalog",
       "exception_message":"Table with name nonexistent_tbl does not exist!\nDid you mean \"temp.information_schema.tables\"?",
       "name":"nonexistent_tbl",
       "candidates":"temp.information_schema.tables",
       "position":"14",
       "type":"Table",
       "error_subtype":"MISSING_ENTRY"
    }

## IEEE Floating-Point Operation Semantics

DuckDB follows IEEE floating-point operation semantics. If you would like to turn this off, run:

    SET ieee_floating_point_ops = false;

In this case, floating point division by zero (e.g., `1.0 / 0.0`, `0.0 / 0.0` and `-1.0 / 0.0`) will all return `NULL`.

## Query Verification (for Development)

The following `PRAGMA`s are mostly used for development and internal testing.

Enable query verification:

    PRAGMA enable_verification;

Disable query verification:

    PRAGMA disable_verification;

Enable force parallel query processing:

    PRAGMA verify_parallelism;

Disable force parallel query processing:

    PRAGMA disable_verify_parallelism;

## Block Sizes

When persisting a database to disk, DuckDB writes to a dedicated file containing a list of blocks holding the data. In the case of a file that only holds very little data, e.g., a small table, the default block size of 256KB might not be ideal. Therefore, DuckDB's storage format supports different block sizes.

There are a few constraints on possible block size values.

- Must be a power of two.
- Must be greater or equal to 16384 (16 KB).
- Must be lesser or equal to 262144 (256 KB).

You can set the default block size for all new DuckDB files created by an instance like so:

    SET default_block_size = '16384';

It is also possible to set the block size on a per-file basis, see `ATTACH` for details.

##### About this page

- Report content issue
- Edit this page on GitHub