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

CREATE TABLE Statement

The `CREATE TABLE` statement creates a table in the catalog.

## Examples

Create a table with two integer columns (`i` and `j`):

    CREATE TABLE t1 (i INTEGER, j INTEGER);

Create a table with a primary key:

    CREATE TABLE t1 (id INTEGER PRIMARY KEY, j VARCHAR);

Create a table with a composite primary key:

    CREATE TABLE t1 (id INTEGER, j VARCHAR, PRIMARY KEY (id, j));

Create a table with various different types and constraints:

    CREATE TABLE t1 (
        i INTEGER NOT NULL,
        decimalnr DOUBLE CHECK (decimalnr < 10),
        date DATE UNIQUE,
        time TIMESTAMP
    );

Create table with `CREATE TABLE ... AS SELECT` (CTAS):

    CREATE TABLE t1 AS
        SELECT 42 AS i, 84 AS j;

Create a table from a CSV file (automatically detecting column names and types):

    CREATE TABLE t1 AS
        SELECT *
        FROM read_csv('path/file.csv');

We can use the `FROM`-first syntax to omit `SELECT *`:

    CREATE TABLE t1 AS
        FROM read_csv('path/file.csv');

Copy the schema of `t2` to `t1`:

    CREATE TABLE t1 AS
        FROM t2
        LIMIT 0;

## Temporary Tables

Temporary tables can be created using the `CREATE TEMP TABLE` or the `CREATE TEMPORARY TABLE` statement (see diagram below). Temporary tables are session scoped (similar to PostgreSQL for example), meaning that only the specific connection that created them can access them, and once the connection to DuckDB is closed they will be automatically dropped. Temporary tables reside in memory rather than on disk (even when connecting to a persistent DuckDB), but if the `temp_directory` configuration is set when connecting or with a `SET` command, data will be spilled to disk if memory becomes constrained.

Create a temporary table from a CSV file (automatically detecting column names and types):

    CREATE TEMP TABLE t1 AS
        SELECT *
        FROM read_csv('path/file.csv');

Allow temporary tables to off-load excess memory to disk:

    SET temp_directory = '/path/to/directory/';

Temporary tables are part of the `temp.main` schema. While discouraged, their names can overlap with the names of the regular database tables. In these cases, use their fully qualified name, e.g., `temp.main.t1`, for disambiguation.

## `CREATE OR REPLACE`

The `CREATE OR REPLACE` syntax allows a new table to be created or for an existing table to be overwritten by the new table. This is shorthand for dropping the existing table and then creating the new one.

Create a table with two integer columns (i and j) even if t1 already exists:

    CREATE OR REPLACE TABLE t1 (i INTEGER, j INTEGER);

## `IF NOT EXISTS`

The `IF NOT EXISTS` syntax will only proceed with the creation of the table if it does not already exist. If the table already exists, no action will be taken and the existing table will remain in the database.

Create a table with two integer columns (`i` and `j`) only if `t1` does not exist yet:

    CREATE TABLE IF NOT EXISTS t1 (i INTEGER, j INTEGER);

## `CREATE TABLE ... AS SELECT` (CTAS)

DuckDB supports the `CREATE TABLE ... AS SELECT` syntax, also known as “CTAS”:

    CREATE TABLE nums AS
        SELECT i
        FROM range(0, 3) t(i);

This syntax can be used in combination with the CSV reader, the shorthand to read directly from CSV files without specifying a function, the `FROM`-first syntax, and the HTTP(S) support, yielding concise SQL commands such as the following:

    CREATE TABLE flights AS
        FROM 'https://duckdb.org/data/flights.csv';

The CTAS construct also works with the `OR REPLACE` modifier, yielding `CREATE OR REPLACE TABLE ... AS` statements:

    CREATE OR REPLACE TABLE flights AS
        FROM 'https://duckdb.org/data/flights.csv';

Note that it is not possible to create tables using CTAS statements with constraints (primary keys, check constraints, etc.).

## Check Constraints

A `CHECK` constraint is an expression that must be satisfied by the values of every row in the table.

    CREATE TABLE t1 (
        id INTEGER PRIMARY KEY,
        percentage INTEGER CHECK (0 <= percentage AND percentage <= 100)
    );
    INSERT INTO t1 VALUES (1, 5);
    INSERT INTO t1 VALUES (2, -1);

    Constraint Error: CHECK constraint failed: t1

    INSERT INTO t1 VALUES (3, 101);

    Constraint Error: CHECK constraint failed: t1

    CREATE TABLE t2 (id INTEGER PRIMARY KEY, x INTEGER, y INTEGER CHECK (x < y));
    INSERT INTO t2 VALUES (1, 5, 10);
    INSERT INTO t2 VALUES (2, 5, 3);

    Constraint Error: CHECK constraint failed: t2

`CHECK` constraints can also be added as part of the `CONSTRAINTS` clause:

    CREATE TABLE t3 (
        id INTEGER PRIMARY KEY,
        x INTEGER,
        y INTEGER,
        CONSTRAINT x_smaller_than_y CHECK (x < y)
    );
    INSERT INTO t3 VALUES (1, 5, 10);
    INSERT INTO t3 VALUES (2, 5, 3);

    Constraint Error: CHECK constraint failed: t3

## Foreign Key Constraints

A `FOREIGN KEY` is a column (or set of columns) that references another table's primary key. Foreign keys check referential integrity, i.e., the referred primary key must exist in the other table upon insertion.

    CREATE TABLE t1 (id INTEGER PRIMARY KEY, j VARCHAR);
    CREATE TABLE t2 (
        id INTEGER PRIMARY KEY,
        t1_id INTEGER,
        FOREIGN KEY (t1_id) REFERENCES t1 (id)
    );

Example:

    INSERT INTO t1 VALUES (1, 'a');
    INSERT INTO t2 VALUES (1, 1);
    INSERT INTO t2 VALUES (2, 2);

    Constraint Error: Violates foreign key constraint because key "id: 2" does not exist in the referenced table

Foreign keys can be defined on composite primary keys:

    CREATE TABLE t3 (id INTEGER, j VARCHAR, PRIMARY KEY (id, j));
    CREATE TABLE t4 (
        id INTEGER PRIMARY KEY, t3_id INTEGER, t3_j VARCHAR,
        FOREIGN KEY (t3_id, t3_j) REFERENCES t3(id, j)
    );

Example:

    INSERT INTO t3 VALUES (1, 'a');
    INSERT INTO t4 VALUES (1, 1, 'a');
    INSERT INTO t4 VALUES (2, 1, 'b');

    Constraint Error: Violates foreign key constraint because key "id: 1, j: b" does not exist in the referenced table

Foreign keys can also be defined on unique columns:

    CREATE TABLE t5 (id INTEGER UNIQUE, j VARCHAR);
    CREATE TABLE t6 (
        id INTEGER PRIMARY KEY,
        t5_id INTEGER,
        FOREIGN KEY (t5_id) REFERENCES t5(id)
    );

### Limitations

Foreign keys have the following limitations.

Foreign keys with cascading deletes (`FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE`) are not supported.

Inserting into tables with self-referencing foreign keys is currently not supported and will result in the following error:

    Constraint Error: Violates foreign key constraint because key "..." does not exist in the referenced table.

## Generated Columns

The `[type] [GENERATED ALWAYS] AS (expr) [VIRTUAL|STORED]` syntax will create a generated column. The data in this kind of column is generated from its expression, which can reference other (regular or generated) columns of the table. Since they are produced by calculations, these columns can not be inserted into directly.

DuckDB can infer the type of the generated column based on the expression's return type. This allows you to leave out the type when declaring a generated column. It is possible to explicitly set a type, but insertions into the referenced columns might fail if the type can not be cast to the type of the generated column.

Generated columns come in two varieties: `VIRTUAL` and `STORED`. The data of virtual generated columns is not stored on disk, instead it is computed from the expression every time the column is referenced (through a select statement).

The data of stored generated columns is stored on disk and is computed every time the data of their dependencies change (through an `INSERT` / `UPDATE` / `DROP` statement).

Currently, only the `VIRTUAL` kind is supported, and it is also the default option if the last field is left blank.

The simplest syntax for a generated column:

The type is derived from the expression, and the variant defaults to `VIRTUAL`:

    CREATE TABLE t1 (x FLOAT, two_x AS (2 * x));

Fully specifying the same generated column for completeness:

    CREATE TABLE t1 (x FLOAT, two_x FLOAT GENERATED ALWAYS AS (2 * x) VIRTUAL);

## Syntax

##### About this page

- Report content issue
- Edit this page on GitHub