Duckdb Knowledge Patch

DuckDB Knowledge Patch

Use this skill when writing DuckDB SQL, upgrading an existing database, working with external files or lakehouse formats, configuring the CLI, or using spatial data. Inspect the project's DuckDB version and load the relevant topic reference before relying on defaults, return types, parser behavior, storage compatibility, or extension behavior.

How to apply this skill

1. Determine the exact DuckDB library, CLI, and extension versions in use.
2. Review the compatibility checks below before changing existing SQL or files.
3. Treat database storage versions separately from the engine version.
4. Check extension and platform requirements before generating deployment steps.
5. Load the topic reference that matches the task; it contains the complete semantics and directional caveats behind the quick reference.
6. Prefer project manifests, code, tests, and observed behavior if the project is newer than the frontmatter version.

Reference index

Compatibility checks first

Account for changed SQL results and types

• date_trunc on a DATE returns TIMESTAMP. Add an explicit cast when an application, schema, or client contract still requires DATE.
• map['key'] returns the value itself and is equivalent to map_extract_value. Use map_extract only when the required result is a single-element list.
• current_time and current_date use the local timezone and require ICU.
• A fixed random seed does not reproduce sequences from engines that used the earlier generator state.
• Nested-structure serialization escapes quotes so values round-trip. Use array_to_string when code deliberately needs the old unquoted display.

SELECT typeof(date_trunc('month', DATE '2026-03-27')); -- TIMESTAMP
SELECT map(['k'], ['v'])['k'];                         -- 'v'
SELECT map_extract(map(['k'], ['v']), 'k');           -- ['v']

Migrate deprecated lambda syntax

Prefer Python-style lambdas:

SELECT list_transform([1, 2, 3], lambda x: x + 1);

The single-arrow form x -> x + 1 warns in 1.5. Use lambda_syntax only as a deliberate migration control:

SET lambda_syntax = 'ENABLE_SINGLE_ARROW';
SET lambda_syntax = 'DISABLE_SINGLE_ARROW';

The arrow form is scheduled to be disabled by default in 2.0. Do not introduce new uses merely because the compatibility setting can suppress the warning.

Make spatial axis order explicit

Distance, perimeter, area, within, and ST_Transform operations are migrating from legacy latitude/longitude coordinates to conventional x/y ordering. Set the behavior instead of accepting an unset-value warning:

SET geometry_always_xy = true;  -- x = longitude, y = latitude

Use false only to pin legacy x-as-latitude behavior during migration. The unset state is scheduled to become an error in 2.0, and true is scheduled to become the default in 2.1.

Audit parser and identifier assumptions

• AT and LAMBDA are reserved identifiers and must be quoted when used as names.
• GRANT is no longer reserved.
• RFC 4180-style strict CSV parsing is on by default. Opt out per irregular input with strict_mode = false; do not disable it globally by habit.

Check runtime and release-line support

• DuckDB 1.5 is a non-LTS release line scheduled to reach end of life on 2026-09-01.
• DuckDB 1.4 is an LTS line supported through September 2026.
• Beginning with 1.4, every other release line is LTS and receives one year of community support.
• Official Linux binaries from 1.3 require glibc 2.28 or newer. Extensions are no longer published for linux_amd64_gcc4; older systems must build from source.

High-value SQL and type features

Store heterogeneous values with VARIANT

VARIANT stores a typed binary value per row rather than JSON text. Cast scalars, lists, and structs explicitly, inspect their type, and extract fields:

CREATE TABLE events (id INTEGER, data VARIANT);
INSERT INTO events VALUES
  (1, {'name': 'Alice', 'age': 30}::VARIANT);

SELECT variant_typeof(data), variant_extract(data, 'name')
FROM events;

Dot notation is also available for nested access. DuckDB reads Parquet VARIANT columns, including shredded data. DuckLake and Delta support the type; Iceberg support begins in 1.5.1.

Query database files without attaching them

Use read_duckdb when the task is a direct scan rather than a persistent catalog attachment. It accepts glob patterns:

SELECT min(i), max(i)
FROM read_duckdb('numbers*.db');

Expand transformed column sets

UNPACK expands a computed COLUMNS(*) expression after operations such as a cast, which the older leading *COLUMNS(...) form cannot do:

SELECT [UNPACK(COLUMNS(*)::VARCHAR)] AS values
FROM tbl;

Keep credentials out of literal SQL

Fields in CREATE SECRET accept scalar expressions. Read a value from an environment variable or another expression instead of embedding it in SQL that may be logged:

CREATE SECRET http (
    TYPE http,
    BEARER_TOKEN getenv('MY_SECRET')
);

Storage and security decisions

Choose file compatibility deliberately

New storage features do not automatically change the file format. Opt in by attaching with STORAGE_VERSION; that version becomes the oldest engine that can open the file:

ATTACH 'modern.db' (STORAGE_VERSION 'v1.2.0');
SELECT database_name, tags FROM duckdb_databases();

To make an older-compatible copy, attach the destination with its required storage version and copy from the source database to that destination.

Encrypt all database-managed files

ENCRYPTION_KEY on ATTACH encrypts the database, WAL, and temporary files. AES-256-GCM is the default. Loading httpfs selects the hardware-accelerated OpenSSL implementation instead of the built-in implementation:

LOAD httpfs;
ATTACH 'encrypted.db' AS enc_db (ENCRYPTION_KEY 'quack_quack');

Understand remote caching and HTTP behavior

Remote file data is cached automatically within the global memory limit. Inspect it with duckdb_external_file_cache() or disable it for one connection:

FROM duckdb_external_file_cache();
SET enable_external_file_cache = false;

The default httpfs backend uses curl. OpenSSL and settings such as http_timeout and http_retries remain available. Once curl-backed httpfs is loaded, later extension installations use HTTPS through it; downloading httpfs itself still uses httplib.

CLI essentials

Reuse the previous query result as table _ instead of rerunning work:

FROM ducks WHERE extinct_year IS NOT NULL;
FROM _;

For untrusted or constrained interactive work, start with -safe or enter .safe_mode; safe mode blocks host-file-system interaction except for the database initially opened.

Passing a Parquet, CSV, or JSON file in the database-path position creates a temporary in-memory database with a file view and another view named after the file stem:

duckdb region.parquet -c 'FROM region;'

Load the CLI reference before choosing a package or installer, especially on musl Linux, older glibc systems, or Windows.

Integration direction matters

• COPY writes to Azure Blob Storage through az:// and ADLSv2 through abfss://.
• Copying an entire Iceberg database into DuckDB or DuckLake works directly. When copying in the other direction, create destination schemas first.
• Iceberg CREATE TABLE ... WITH (...) accepts table properties, with format-version and location promoted to dedicated fields.
• Load the lakehouse reference for DuckLake specification behavior, Delta write guarantees, Iceberg catalog headers, ODBC connection objects, and the Teradata connector.

For edge semantics, upgrade planning, storage compatibility, or connector configuration, read the full topic reference rather than relying only on this quick reference.