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

Configuration

DuckDB has a number of configuration options that can be used to change the behavior of the system.

The configuration options can be set using either the `SET` statement or the `PRAGMA` statement. They can be reset to their original values using the `RESET` statement.

The values of configuration options can be queried via the `current_setting()` scalar function or using the `duckdb_settings()` table function. For example:

    SELECT current_setting('memory_limit') AS memlimit;
    SELECT value AS memlimit FROM duckdb_settings() WHERE name = 'memory_limit';

## Examples

Set the memory limit of the system to 10 GB.

    SET memory_limit = '10GB';

Configure the system to use 1 thread.

    SET threads TO 1;

Enable printing of a progress bar during long-running queries.

    SET enable_progress_bar = true;

Set the default null order to `NULLS LAST`.

    SET default_null_order = 'nulls_last';

Return the current value of a specific setting.

    SELECT current_setting('threads') AS threads;

threads
10

Query a specific setting.

    SELECT *
    FROM duckdb_settings()
    WHERE name = 'threads';

name | value | description | input_type | scope
--- | --- | --- | --- | ---
threads | 1 | The number of total threads used by the system. | BIGINT | GLOBAL

Show a list of all available settings.

    SELECT *
    FROM duckdb_settings();

Reset the memory limit of the system back to the default.

    RESET memory_limit;

## Secrets Manager

DuckDB has a Secrets manager, which provides a unified user interface for secrets across all backends (e.g., AWS S3) that use them.

## Configuration Reference

Configuration options come with different default scopes: `GLOBAL` and `LOCAL`. Below is a list of all available configuration options by scope.

### Global Configuration Options

Name | Description | Type | Default value
--- | --- | --- | ---
`Calendar` | The current calendar | `VARCHAR` | System (locale) calendar
`TimeZone` | The current time zone | `VARCHAR` | System (locale) timezone
`access_mode` | Access mode of the database (`AUTOMATIC`, `READ_ONLY` or `READ_WRITE`) | `VARCHAR` | `automatic`
`allocator_background_threads` | Whether to enable the allocator background thread. | `BOOLEAN` | `false`
`allocator_bulk_deallocation_flush_threshold` | If a bulk deallocation larger than this occurs, flush outstanding allocations. | `VARCHAR` | `512.0 MiB`
`allocator_flush_threshold` | Peak allocation threshold at which to flush the allocator after completing a task. | `VARCHAR` | `128.0 MiB`
`allow_community_extensions` | Allow to load community built extensions | `BOOLEAN` | `true`
`allow_extensions_metadata_mismatch` | Allow to load extensions with not compatible metadata | `BOOLEAN` | `false`
`allow_persistent_secrets` | Allow the creation of persistent secrets, that are stored and loaded on restarts | `BOOLEAN` | `true`
`allow_unredacted_secrets` | Allow printing unredacted secrets | `BOOLEAN` | `false`
`allow_unsigned_extensions` | Allow to load extensions with invalid or missing signatures | `BOOLEAN` | `false`
`arrow_large_buffer_size` | Whether arrow buffers for strings, blobs, uuids and bits should be exported using large buffers | `BOOLEAN` | `false`
`arrow_lossless_conversion` | Whenever a DuckDB type does not have a clear native or canonical extension match in Arrow, export the types with a duckdb.type_name extension name. | `BOOLEAN` | `false`
`arrow_output_list_view` | Whether export to arrow format should use ListView as the physical layout for LIST columns | `BOOLEAN` | `false`
`autoinstall_extension_repository` | Overrides the custom endpoint for extension installation on autoloading | `VARCHAR` |
`autoinstall_known_extensions` | Whether known extensions are allowed to be automatically installed when a query depends on them | `BOOLEAN` | `true`
`autoload_known_extensions` | Whether known extensions are allowed to be automatically loaded when a query depends on them | `BOOLEAN` | `true`
`binary_as_string` | In Parquet files, interpret binary data as a string. | `BOOLEAN` |
`ca_cert_file` | Path to a custom certificate file for self-signed certificates. | `VARCHAR` |
`catalog_error_max_schemas` | The maximum number of schemas the system will scan for "did you mean…" style errors in the catalog | `UBIGINT` | `100`
`checkpoint_threshold`, `wal_autocheckpoint` | The WAL size threshold at which to automatically trigger a checkpoint (e.g., 1GB) | `VARCHAR` | `16.0 MiB`
`custom_extension_repository` | Overrides the custom endpoint for remote extension installation | `VARCHAR` |
`custom_user_agent` | Metadata from DuckDB callers | `VARCHAR` |
`default_block_size` | The default block size for new duckdb database files (new as-in, they do not yet exist). | `UBIGINT` | `262144`
`default_collation` | The collation setting used when none is specified | `VARCHAR` |
`default_null_order`, `null_order` | Null ordering used when none is specified (`NULLS_FIRST`, `NULLS_LAST`, `NULLS_FIRST_ON_ASC_LAST_ON_DESC` or `NULLS_LAST_ON_ASC_FIRST_ON_DESC`) | `VARCHAR` | `NULLS_LAST`
`default_order` | The order type used when none is specified (`ASC` or `DESC`) | `VARCHAR` | `ASC`
`default_secret_storage` | Allows switching the default storage for secrets | `VARCHAR` | `local_file`
`disabled_filesystems` | Disable specific file systems preventing access (e.g., LocalFileSystem) | `VARCHAR` |
`duckdb_api` | DuckDB API surface | `VARCHAR` | `cli`
`enable_external_access` | Allow the database to access external state (through e.g., loading/installing modules, COPY TO/FROM, CSV readers, pandas replacement scans, etc) | `BOOLEAN` | `true`
`enable_fsst_vectors` | Allow scans on FSST compressed segments to emit compressed vectors to utilize late decompression | `BOOLEAN` | `false`
`enable_http_metadata_cache` | Whether or not the global http metadata is used to cache HTTP metadata | `BOOLEAN` | `false`
`enable_macro_dependencies` | Enable created MACROs to create dependencies on the referenced objects (such as tables) | `BOOLEAN` | `false`
`enable_object_cache` | Whether or not object cache is used to cache e.g., Parquet metadata | `BOOLEAN` | `false`
`enable_server_cert_verification` | Enable server side certificate verification. | `BOOLEAN` | `false`
`enable_view_dependencies` | Enable created VIEWs to create dependencies on the referenced objects (such as tables) | `BOOLEAN` | `false`
`extension_directory` | Set the directory to store extensions in | `VARCHAR` |
`external_threads` | The number of external threads that work on DuckDB tasks. | `BIGINT` | `1`
`force_download` | Forces upfront download of file | `BOOLEAN` | `false`
`http_keep_alive` | Keep alive connections. Setting this to false can help when running into connection failures | `BOOLEAN` | `true`
`http_proxy_password` | Password for HTTP proxy | `VARCHAR` |
`http_proxy_username` | Username for HTTP proxy | `VARCHAR` |
`http_proxy` | HTTP proxy host | `VARCHAR` |
`http_retries` | HTTP retries on I/O error | `UBIGINT` | `3`
`http_retry_backoff` | Backoff factor for exponentially increasing retry wait time | `FLOAT` | `4`
`http_retry_wait_ms` | Time between retries | `UBIGINT` | `100`
`http_timeout` | HTTP timeout read/write/connection/retry | `UBIGINT` | `30000`
`immediate_transaction_mode` | Whether transactions should be started lazily when needed, or immediately when BEGIN TRANSACTION is called | `BOOLEAN` | `false`
`index_scan_max_count` | The maximum index scan count sets a threshold for index scans. If fewer than MAX(index_scan_max_count, index_scan_percentage * total_row_count) rows match, we perform an index scan instead of a table scan. | `UBIGINT` | `2048`
`index_scan_percentage` | The index scan percentage sets a threshold for index scans. If fewer than MAX(index_scan_max_count, index_scan_percentage * total_row_count) rows match, we perform an index scan instead of a table scan. | `DOUBLE` | `0.001`
`lock_configuration` | Whether or not the configuration can be altered | `BOOLEAN` | `false`
`max_memory`, `memory_limit` | The maximum memory of the system (e.g., 1GB) | `VARCHAR` | 80% of RAM
`max_temp_directory_size` | The maximum amount of data stored inside the 'temp_directory'. The default value of `0 bytes` is a placeholder to mean that the entire available disk space on that drive may be used. To actually limit the `temp_directory` to `0 bytes`, set `temp_directory` to `NULL` or the empty string. | `VARCHAR` | `0 bytes`
`max_vacuum_tasks` | The maximum vacuum tasks to schedule during a checkpoint | `UBIGINT` | `100`
`old_implicit_casting` | Allow implicit casting to/from VARCHAR | `BOOLEAN` | `false`
`password` | The password to use. Ignored for legacy compatibility. | `VARCHAR` | `NULL`
`preserve_insertion_order` | Whether or not to preserve insertion order. If set to false the system is allowed to re-order any results that do not contain ORDER BY clauses. | `BOOLEAN` | `true`
`produce_arrow_string_view` | Whether strings should be produced by DuckDB in Utf8View format instead of Utf8 | `BOOLEAN` | `false`
`s3_access_key_id` | S3 Access Key ID | `VARCHAR` |
`s3_endpoint` | S3 Endpoint | `VARCHAR` |
`s3_region` | S3 Region | `VARCHAR` | `us-east-1`
`s3_secret_access_key` | S3 Access Key | `VARCHAR` |
`s3_session_token` | S3 Session Token | `VARCHAR` |
`s3_uploader_max_filesize` | S3 Uploader max filesize (between 50GB and 5TB) | `VARCHAR` | `800GB`
`s3_uploader_max_parts_per_file` | S3 Uploader max parts per file (between 1 and 10000) | `UBIGINT` | `10000`
`s3_uploader_thread_limit` | S3 Uploader global thread limit | `UBIGINT` | `50`
`s3_url_compatibility_mode` | Disable Globs and Query Parameters on S3 URLs | `BOOLEAN` | `false`
`s3_url_style` | S3 URL style | `VARCHAR` | `vhost`
`s3_use_ssl` | S3 use SSL | `BOOLEAN` | `true`
`secret_directory` | Set the directory to which persistent secrets are stored | `VARCHAR` | `~/.duckdb/stored_secrets`
`storage_compatibility_version` | Serialize on checkpoint with compatibility for a given duckdb version | `VARCHAR` | `v0.10.2`
`temp_directory` | Set the directory to which to write temp files. Set to `NULL` or empty string to disable. | `VARCHAR` | `⟨database_name⟩.tmp` or `.tmp` (in in-memory mode)
`threads`, `worker_threads` | The number of total threads used by the system. | `BIGINT` | # CPU cores
`username`, `user` | The username to use. Ignored for legacy compatibility. | `VARCHAR` | `NULL`

### Local Configuration Options

Name | Description | Type | Default value
--- | --- | --- | ---
`custom_profiling_settings` | Accepts a `JSON` enabling custom metrics | `VARCHAR` | `{"RESULT_SET_SIZE": "true", "OPERATOR_TIMING": "true", "OPERATOR_ROWS_SCANNED": "true", "CUMULATIVE_ROWS_SCANNED": "true", "OPERATOR_CARDINALITY": "true", "OPERATOR_TYPE": "true", "CUMULATIVE_CARDINALITY": "true", "EXTRA_INFO": "true", "CPU_TIME": "true", "BLOCKED_THREAD_TIME": "true", "QUERY_NAME": "true"}`
`enable_http_logging` | Enables HTTP logging | `BOOLEAN` | `false`
`enable_profiling` | Enables profiling, and sets the output format (`JSON`, `QUERY_TREE`, `QUERY_TREE_OPTIMIZER`) | `VARCHAR` | `NULL`
`enable_progress_bar_print` | Controls the printing of the progress bar, when 'enable_progress_bar' is true | `BOOLEAN` | `true`
`enable_progress_bar` | Enables the progress bar, printing progress to the terminal for long queries | `BOOLEAN` | `true`
`errors_as_json` | Output error messages as structured `JSON` instead of as a raw string | `BOOLEAN` | `false`
`explain_output` | Output of EXPLAIN statements (`ALL`, `OPTIMIZED_ONLY`, `PHYSICAL_ONLY`) | `VARCHAR` | `physical_only`
`file_search_path` | A comma separated list of directories to search for input files | `VARCHAR` |
`home_directory` | Sets the home directory used by the system | `VARCHAR` |
`http_logging_output` | The file to which HTTP logging output should be saved, or empty to print to the terminal | `VARCHAR` |
`ieee_floating_point_ops` | Use IEE754-compliant floating point operations (returning NAN instead of errors/NULL) | `BOOLEAN` | `true`
`integer_division` | Whether or not the / operator defaults to integer division, or to floating point division | `BOOLEAN` | `false`
`log_query_path` | Specifies the path to which queries should be logged (default: NULL, queries are not logged) | `VARCHAR` | `NULL`
`max_expression_depth` | The maximum expression depth limit in the parser. WARNING: increasing this setting and using very deep expressions might lead to stack overflow errors. | `UBIGINT` | `1000`
`merge_join_threshold` | The number of rows we need on either table to choose a merge join | `UBIGINT` | `1000`
`nested_loop_join_threshold` | The number of rows we need on either table to choose a nested loop join | `UBIGINT` | `5`
`order_by_non_integer_literal` | Allow ordering by non-integer literals - ordering by such literals has no effect | `BOOLEAN` | `false`
`ordered_aggregate_threshold` | The number of rows to accumulate before sorting, used for tuning | `UBIGINT` | `262144`
`partitioned_write_flush_threshold` | The threshold in number of rows after which we flush a thread state when writing using `PARTITION_BY` | `UBIGINT` | `524288`
`partitioned_write_max_open_files` | The maximum amount of files the system can keep open before flushing to disk when writing using `PARTITION_BY` | `UBIGINT` | `100`
`perfect_ht_threshold` | Threshold in bytes for when to use a perfect hash table | `BIGINT` | `12`
`pivot_filter_threshold` | The threshold to switch from using filtered aggregates to LIST with a dedicated pivot operator | `BIGINT` | `20`
`pivot_limit` | The maximum number of pivot columns in a pivot statement | `BIGINT` | `100000`
`prefer_range_joins` | Force use of range joins with mixed predicates | `BOOLEAN` | `false`
`preserve_identifier_case` | Whether or not to preserve the identifier case, instead of always lowercasing all non-quoted identifiers | `BOOLEAN` | `true`
`profile_output`, `profiling_output` | The file to which profile output should be saved, or empty to print to the terminal | `VARCHAR` |
`profiling_mode` | The profiling mode (`STANDARD` or `DETAILED`) | `VARCHAR` | `NULL`
`progress_bar_time` | Sets the time (in milliseconds) how long a query needs to take before we start printing a progress bar | `BIGINT` | `2000`
`scalar_subquery_error_on_multiple_rows` | When a scalar subquery returns multiple rows - return a random row instead of returning an error | `BOOLEAN` | `true`
`schema` | Sets the default search schema. Equivalent to setting search_path to a single value. | `VARCHAR` | `main`
`search_path` | Sets the default catalog search path as a comma-separated list of values | `VARCHAR` |
`streaming_buffer_size` | The maximum memory to buffer between fetching from a streaming result (e.g., 1GB) | `VARCHAR` | `976.5 KiB`

## Pages in This Section

##### About this page

- Report content issue
- Edit this page on GitHub