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

Dot Commands

Dot commands are available in the DuckDB CLI client. To use one of these commands, begin the line with a period (`.`) immediately followed by the name of the command you wish to execute. Additional arguments to the command are entered, space separated, after the command. If an argument must contain a space, either single or double quotes may be used to wrap that parameter. Dot commands must be entered on a single line, and no whitespace may occur before the period. No semicolon is required at the end of the line. To see available commands, use the `.help` command.

## Dot Commands

Command | Description
--- | ---
`.bail on|off` | Stop after hitting an error. Default: `off`
`.binary on|off` | Turn binary output on or off. Default: `off`
`.cd DIRECTORY` | Change the working directory to `DIRECTORY`
`.changes on|off` | Show number of rows changed by SQL
`.check GLOB` | Fail if output since .testcase does not match
`.columns` | Column-wise rendering of query results
`.constant ?COLOR?` | Sets the syntax highlighting color used for constant values
`.constantcode ?CODE?` | Sets the syntax highlighting terminal code used for constant values
`.databases` | List names and files of attached databases
`.echo on|off` | Turn command echo on or `off`
`.excel` | Display the output of next command in spreadsheet
`.exit ?CODE?` | Exit this program with return-code `CODE`
`.explain ?on|off|auto?` | Change the `EXPLAIN` formatting mode. Default: `auto`
`.fullschema ?--indent?` | Show schema and the content of `sqlite_stat` tables
`.headers on|off` | Turn display of headers on or `off`
`.help ?-all? ?PATTERN?` | Show help text for `PATTERN`
`.highlight [on|off]` | Toggle syntax highlighting in the shell `on`/`off`
`.import FILE TABLE` | Import data from `FILE` into `TABLE`
`.indexes ?TABLE?` | Show names of indexes
`.keyword ?COLOR?` | Sets the syntax highlighting color used for keywords
`.keywordcode ?CODE?` | Sets the syntax highlighting terminal code used for keywords
`.lint OPTIONS` | Report potential schema issues.
`.log FILE|off` | Turn logging on or off. `FILE` can be `stderr`/`stdout`
`.maxrows COUNT` | Sets the maximum number of rows for display. Only for duckbox mode
`.maxwidth COUNT` | Sets the maximum width in characters. 0 defaults to terminal width. Only for duckbox mode
`.mode MODE ?TABLE?` | Set output mode
`.multiline` | Set multi-line mode (default)
`.nullvalue STRING` | Use `STRING` in place of `NULL` values
`.once ?OPTIONS? ?FILE?` | Output for the next SQL command only to `FILE`
`.open ?OPTIONS? ?FILE?` | Close existing database and reopen `FILE`
`.output ?FILE?` | Send output to `FILE` or `stdout` if `FILE` is omitted
`.parameter CMD ...` | Manage SQL parameter bindings
`.print STRING...` | Print literal `STRING`
`.prompt MAIN CONTINUE` | Replace the standard prompts
`.quit` | Exit this program
`.read FILE` | Read input from `FILE`
`.rows` | Row-wise rendering of query results (default)
`.schema ?PATTERN?` | Show the `CREATE` statements matching `PATTERN`
`.separator COL ?ROW?` | Change the column and row separators
`.sha3sum ...` | Compute a SHA3 hash of database content
`.shell CMD ARGS...` | Run `CMD ARGS...` in a system shell
`.show` | Show the current values for various settings
`.singleline` | Set single-line mode
`.system CMD ARGS...` | Run `CMD ARGS...` in a system shell
`.tables ?TABLE?` | List names of tables matching LIKE pattern `TABLE`
`.testcase NAME` | Begin redirecting output to `NAME`
`.timer on|off` | Turn SQL timer on or off. SQL statements separated by `;` but not separated via newline are measured together.
`.width NUM1 NUM2 ...` | Set minimum column widths for columnar output

## Using the `.help` Command

The `.help` text may be filtered by passing in a text string as the second argument.

    .help m

    .maxrows COUNT      Sets the maximum number of rows for display (default: 40). Only for duckbox mode.
    .maxwidth COUNT     Sets the maximum width in characters. 0 defaults to terminal width. Only for duckbox mode.
    .mode MODE ?TABLE?  Set output mode

### `.output`: Writing Results to a File

By default, the DuckDB CLI sends results to the terminal's standard output. However, this can be modified using either the `.output` or `.once` commands. Pass in the desired output file location as a parameter. The `.once` command will only output the next set of results and then revert to standard out, but `.output` will redirect all subsequent output to that file location. Note that each result will overwrite the entire file at that destination. To revert back to standard output, enter `.output` with no file parameter.

In this example, the output format is changed to `markdown`, the destination is identified as a Markdown file, and then DuckDB will write the output of the SQL statement to that file. Output is then reverted to standard output using `.output` with no parameter.

    .mode markdown
    .output my_results.md
    SELECT 'taking flight' AS output_column;
    .output
    SELECT 'back to the terminal' AS displayed_column;

The file `my_results.md` will then contain:

    | output_column |
    |---------------|
    | taking flight |

The terminal will then display:

    |   displayed_column   |
    |----------------------|
    | back to the terminal |

A common output format is CSV, or comma separated values. DuckDB supports SQL syntax to export data as CSV or Parquet, but the CLI-specific commands may be used to write a CSV instead if desired.

    .mode csv
    .once my_output_file.csv
    SELECT 1 AS col_1, 2 AS col_2
    UNION ALL
    SELECT 10 AS col1, 20 AS col_2;

The file `my_output_file.csv` will then contain:

    col_1,col_2
    1,2
    10,20

By passing special options (flags) to the `.once` command, query results can also be sent to a temporary file and automatically opened in the user's default program. Use either the `-e` flag for a text file (opened in the default text editor), or the `-x` flag for a CSV file (opened in the default spreadsheet editor). This is useful for more detailed inspection of query results, especially if there is a relatively large result set. The `.excel` command is equivalent to `.once -x`.

    .once -e
    SELECT 'quack' AS hello;

The results then open in the default text file editor of the system, for example:

## Querying the Database Schema

All DuckDB clients support querying the database schema with SQL, but the CLI has additional dot commands that can make it easier to understand the contents of a database. The `.tables` command will return a list of tables in the database. It has an optional argument that will filter the results according to a `LIKE` pattern.

    CREATE TABLE swimmers AS SELECT 'duck' AS animal;
    CREATE TABLE fliers AS SELECT 'duck' AS animal;
    CREATE TABLE walkers AS SELECT 'duck' AS animal;
    .tables

    fliers    swimmers  walkers

For example, to filter to only tables that contain an `l`, use the `LIKE` pattern `%l%`.

    .tables %l%

    fliers   walkers

The `.schema` command will show all of the SQL statements used to define the schema of the database.

    .schema

    CREATE TABLE fliers (animal VARCHAR);
    CREATE TABLE swimmers (animal VARCHAR);
    CREATE TABLE walkers (animal VARCHAR);

## Configuring the Syntax Highlighter

By default the shell includes support for syntax highlighting. The CLI's syntax highlighter can be configured using the following commands.

To turn off the highlighter:

    .highlight off

To turn on the highlighter:

    .highlight on

To configure the color used to highlight constants:

    .constant [red|green|yellow|blue|magenta|cyan|white|brightblack|brightred|brightgreen|brightyellow|brightblue|brightmagenta|brightcyan|brightwhite]

    .constantcode [terminal_code]

To configure the color used to highlight keywords:

    .keyword [red|green|yellow|blue|magenta|cyan|white|brightblack|brightred|brightgreen|brightyellow|brightblue|brightmagenta|brightcyan|brightwhite]

    .keywordcode [terminal_code]

## Importing Data from CSV

> Deprecated This feature is only included for compatibility reasons and may be removed in the future. Use the `read_csv` function or the `COPY` statement to load CSV files.

DuckDB supports SQL syntax to directly query or import CSV files, but the CLI-specific commands may be used to import a CSV instead if desired. The `.import` command takes two arguments and also supports several options. The first argument is the path to the CSV file, and the second is the name of the DuckDB table to create. Since DuckDB requires stricter typing than SQLite (upon which the DuckDB CLI is based), the destination table must be created before using the `.import` command. To automatically detect the schema and create a table from a CSV, see the `read_csv` examples in the import docs.

In this example, a CSV file is generated by changing to CSV mode and setting an output file location:

    .mode csv
    .output import_example.csv
    SELECT 1 AS col_1, 2 AS col_2 UNION ALL SELECT 10 AS col1, 20 AS col_2;

Now that the CSV has been written, a table can be created with the desired schema and the CSV can be imported. The output is reset to the terminal to avoid continuing to edit the output file specified above. The `--skip N` option is used to ignore the first row of data since it is a header row and the table has already been created with the correct column names.

    .mode csv
    .output
    CREATE TABLE test_table (col_1 INTEGER, col_2 INTEGER);
    .import import_example.csv test_table --skip 1

Note that the `.import` command utilizes the current `.mode` and `.separator` settings when identifying the structure of the data to import. The `--csv` option can be used to override that behavior.

    .import import_example.csv test_table --skip 1 --csv

##### About this page

- Report content issue
- Edit this page on GitHub