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

ATTACH and DETACH Statements

DuckDB allows attaching to and detaching from database files.

## Examples

Attach the database `file.db` with the alias inferred from the name (`file`):

    ATTACH 'file.db';

Attach the database `file.db` with an explicit alias (`file_db`):

    ATTACH 'file.db' AS file_db;

Attach the database `file.db` in read only mode:

    ATTACH 'file.db' (READ_ONLY);

Attach the database `file.db` with a block size of 16KB:

    ATTACH 'file.db' (BLOCK_SIZE 16384);

Attach a SQLite database for reading and writing (see the `sqlite` extension for more information):

    ATTACH 'sqlite_file.db' AS sqlite_db (TYPE SQLITE);

Attach the database `file.db` if inferred database alias `file` does not yet exist:

    ATTACH IF NOT EXISTS 'file.db';

Attach the database `file.db` if explicit database alias `file_db` does not yet exist:

    ATTACH IF NOT EXISTS 'file.db' AS file_db;

Create a table in the attached database with alias `file`:

    CREATE TABLE file.new_table (i INTEGER);

Detach the database with alias `file`:

    DETACH file;

Show a list of all attached databases:

    SHOW DATABASES;

Change the default database that is used to the database `file`:

    USE file;

## Attach

The `ATTACH` statement adds a new database file to the catalog that can be read from and written to. Note that attachment definitions are not persisted between sessions: when a new session is launched, you have to re-attach to all databases.

### Attach Syntax

`ATTACH` allows DuckDB to operate on multiple database files, and allows for transfer of data between different database files.

`ATTACH` supports HTTP and S3 endpoints. For these, it creates a read-only connection by default. Therefore, the following two commands are equivalent:

    ATTACH 'https://blobs.duckdb.org/databases/stations.duckdb' AS stations_db;
    ATTACH 'https://blobs.duckdb.org/databases/stations.duckdb' AS stations_db (READ_ONLY);

Similarly, the following two commands connecting to S3 are equivalent:

    ATTACH 's3://duckdb-blobs/databases/stations.duckdb' AS stations_db;
    ATTACH 's3://duckdb-blobs/databases/stations.duckdb' AS stations_db (READ_ONLY);

> Prior to DuckDB version 1.1.0, it was necessary to specify the `READ_ONLY` flag for HTTP and S3 endpoints.

## Detach

The `DETACH` statement allows previously attached database files to be closed and detached, releasing any locks held on the database file.

Note that it is not possible to detach from the default database: if you would like to do so, issue the `USE` statement to change the default database to another one. For example, if you are connected to a persistent database, you may change to an in-memory database by issuing:

    ATTACH ':memory:' AS memory_db;
    USE memory_db;

> Warning Closing the connection, e.g., invoking the `close()` function in Python, does not release the locks held on the database files as the file handles are held by the main DuckDB instance (in Python's case, the `duckdb` module).

### Detach Syntax

## Options

Name | Description | Type | Default value
--- | --- | --- | ---
`access_mode` | Access mode of the database (AUTOMATIC, READ_ONLY, or READ_WRITE) | `VARCHAR` | `automatic`
`type` | The file type (DUCKDB or SQLITE), or deduced from the input string literal (MySQL, PostgreSQL). | `VARCHAR` | `DUCKDB`
`block_size` | The block size of a new database file. Must be a power of two and within [16384, 262144]. Cannot be set for existing files. | `UBIGINT` | `262144`

## Name Qualification

The fully qualified name of catalog objects contains the catalog, the schema and the name of the object. For example:

Attach the database `new_db`:

    ATTACH 'new_db.db';

Create the schema `my_schema` in the database `new_db`:

    CREATE SCHEMA new_db.my_schema;

Create the table `my_table` in the schema `my_schema`:

    CREATE TABLE new_db.my_schema.my_table (col INTEGER);

Refer to the column `col` inside the table `my_table`:

    SELECT new_db.my_schema.my_table.col FROM new_db.my_schema.my_table;

Note that often the fully qualified name is not required. When a name is not fully qualified, the system looks for which entries to reference using the catalog search path. The default catalog search path includes the system catalog, the temporary catalog and the initially attached database together with the `main` schema.

Also note the rules on identifiers and database names in particular.

### Default Database and Schema

When a table is created without any qualifications, the table is created in the default schema of the default database. The default database is the database that is launched when the system is created – and the default schema is `main`.

Create the table `my_table` in the default database:

    CREATE TABLE my_table (col INTEGER);

### Changing the Default Database and Schema

The default database and schema can be changed using the `USE` command.

Set the default database schema to `new_db.main`:

    USE new_db;

Set the default database schema to `new_db.my_schema`:

    USE new_db.my_schema;

### Resolving Conflicts

When providing only a single qualification, the system can interpret this as either a catalog or a schema, as long as there are no conflicts. For example:

    ATTACH 'new_db.db';
    CREATE SCHEMA my_schema;

Creates the table `new_db.main.tbl`:

    CREATE TABLE new_db.tbl (i INTEGER);

Creates the table `default_db.my_schema.tbl`:

    CREATE TABLE my_schema.tbl (i INTEGER);

If we create a conflict (i.e., we have both a schema and a catalog with the same name) the system requests that a fully qualified path is used instead:

    CREATE SCHEMA new_db;
    CREATE TABLE new_db.tbl (i INTEGER);

    Binder Error: Ambiguous reference to catalog or schema "new_db" -
    use a fully qualified path like "memory.new_db"

### Changing the Catalog Search Path

The catalog search path can be adjusted by setting the `search_path` configuration option, which uses a comma-separated list of values that will be on the search path. The following example demonstrates searching in two databases:

    ATTACH ':memory:' AS db1;
    ATTACH ':memory:' AS db2;
    CREATE table db1.tbl1 (i INTEGER);
    CREATE table db2.tbl2 (j INTEGER);

Reference the tables using their fully qualified name:

    SELECT * FROM db1.tbl1;
    SELECT * FROM db2.tbl2;

Or set the search path and reference the tables using their name:

    SET search_path = 'db1,db2';
    SELECT * FROM tbl1;
    SELECT * FROM tbl2;

## Transactional Semantics

When running queries on multiple databases, the system opens separate transactions per database. The transactions are started lazily by default – when a given database is referenced for the first time in a query, a transaction for that database will be started. `SET immediate_transaction_mode = true` can be toggled to change this behavior to eagerly start transactions in all attached databases instead.

While multiple transactions can be active at a time – the system only supports writing to a single attached database in a single transaction. If you try to write to multiple attached databases in a single transaction the following error will be thrown:

    Attempting to write to database "db2" in a transaction that has already modified database "db1" -
    a single transaction can only write to a single attached database.

The reason for this restriction is that the system does not maintain atomicity for transactions across attached databases. Transactions are only atomic within each database file. By restricting the global transaction to write to only a single database file the atomicity guarantees are maintained.

##### About this page

- Report content issue
- Edit this page on GitHub