DuckDB

https://duckdb.org/docs/current/
Admin
DuckDB is a fast, in-process relational database management system designed for analytical queries...
Tokens:1,431,381
Snippets:12,155
Trust Score:9.8
Update:1 week ago
Show doc for...
Tokens:
Raw
### EXPLAIN ANALYZE example with setup and query

Source: https://duckdb.org/docs/current/guides/meta/explain_analyze.html

Example of running EXPLAIN ANALYZE on a query that joins two tables and filters results. The setup creates tables, inserts data, and then runs the EXPLAIN ANALYZE query. The output shows the query plan with run-time statistics.

```sql
CREATE TABLE students (name VARCHAR, sid INTEGER);
CREATE TABLE exams (eid INTEGER, subject VARCHAR, sid INTEGER);
INSERT INTO students VALUES ('Mark', 1), ('Joe', 2), ('Matthew', 3);
INSERT INTO exams VALUES (10, 'Physics', 1), (20, 'Chemistry', 2), (30, 'Literature', 3);

EXPLAIN ANALYZE
    SELECT name
    FROM students
    JOIN exams USING (sid)
    WHERE name LIKE 'Ma%';
```

--------------------------------

### Install Script Output

Source: https://duckdb.org/docs/current/operations_manual/installing_duckdb/install_script

Example output from the DuckDB install script, showing the download progress, installation path, and a hint to add the binary to your PATH.

```text
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  3507  100  3507    0     0  34367      0 --:--:-- --:--:-- --:--:-- 34382
https://install.duckdb.org/v1.4.1/duckdb_cli-osx-universal.gz

*** DuckDB Linux/MacOS installation script, version 1.4.1 ***


         .;odxdl,
       .xXXXXXXXXKc
       0XXXXXXXXXXXd  cooo:
      ,XXXXXXXXXXXXK  OXXXXd
       0XXXXXXXXXXXo  cooo:
       .xXXXXXXXXKc
         .;odxdl,


######################################################################## 100.0%

Successfully installed DuckDB binary to /Users/your_user/.duckdb/cli/1.4.1/duckdb
  with a link from                      /Users/your_user/.duckdb/cli/latest/duckdb

Hint: Append the following line to your shell profile:
export PATH='/Users/your_user/.duckdb/cli/latest':$PATH


To launch DuckDB now, type
/Users/your_user/.duckdb/cli/latest/duckdb

```

--------------------------------

### Install dependencies and build with uv

Source: https://duckdb.org/docs/current/dev/building/python.html

Two-step setup: first installs all development dependencies without building the project, then builds and installs the project without build isolation. This ensures proper CMake integration.

```bash
# Install all development dependencies without building the project
uv sync --no-install-project

# Build and install the project without build isolation
uv sync --no-build-isolation

```

--------------------------------

### UNION BY NAME Example Setup

Source: https://duckdb.org/docs/current/sql/query_syntax/setops.html

Creates the capitals and weather tables used in the UNION BY NAME example. The tables have different columns.

```sql
CREATE TABLE capitals (city VARCHAR, country VARCHAR);
INSERT INTO capitals VALUES
    ('Amsterdam', 'NL'),
    ('Berlin', 'Germany');
CREATE TABLE weather (city VARCHAR, degrees INTEGER, date DATE);
INSERT INTO weather VALUES
    ('Amsterdam', 10, '2022-10-14'),
    ('Seattle', 8, '2022-10-12');
```

--------------------------------

### INSTALL and FORCE INSTALL from local file

Source: https://duckdb.org/docs/current/extensions/troubleshooting

Install an extension from a downloaded file in the Downloads folder, with optional FORCE to overwrite.

```sql
INSTALL '~/Downloads/spatial.duckdb_extension';
-- or
FORCE INSTALL '~/Downloads/spatial.duckdb_extension';

```

--------------------------------

### DuckDB CLI startup output

Source: https://duckdb.org/docs/current/clients/cli/overview.html

Example output when starting the CLI in-memory. It shows the version, usage hints, and the D prompt. Use .open FILENAME to switch to a persistent database.

```text
DuckDB v1.5.6 (Variegata) 069cc9f9b5
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D

```

--------------------------------

### range(start, stop) example

Source: https://duckdb.org/docs/current/sql/functions/list.html

Creates a list from start (inclusive) to stop (exclusive). Default step is 1.

```sql
SELECT range(2, 5);
```

```text
[2, 3, 4]
```

--------------------------------

### Install and Load the Azure Extension

Source: https://duckdb.org/docs/current/core_extensions/azure.html

Manually install and load the 'azure' extension. It is normally autoloaded on first use, but this explicit setup is available if needed.

```sql
INSTALL azure;
LOAD azure;

```

--------------------------------

### Install and load httpfs extension

Source: https://duckdb.org/docs/current/core_extensions/httpfs/overview.html

Manually install and load the httpfs extension. The extension is autoloaded by default on first use, so this is only necessary for manual setup.

```sql
INSTALL httpfs;
LOAD httpfs;

```

--------------------------------

### EXPLAIN with JOIN and FILTER example

Source: https://duckdb.org/docs/current/guides/meta/explain.html

Creates sample tables and runs EXPLAIN on a query that joins students and exams and filters names starting with 'Ma'. The output shows the physical plan with operators like SEQ_SCAN, HASH_JOIN, and PROJECTION, including estimated cardinalities (EC).

```sql
CREATE TABLE students (name VARCHAR, sid INTEGER);
CREATE TABLE exams (eid INTEGER, subject VARCHAR, sid INTEGER);
INSERT INTO students VALUES ('Mark', 1), ('Joe', 2), ('Matthew', 3);
INSERT INTO exams VALUES (10, 'Physics', 1), (20, 'Chemistry', 2), (30, 'Literature', 3);

EXPLAIN
    SELECT name
    FROM students
    JOIN exams USING (sid)
    WHERE name LIKE 'Ma%';

```

```text
┌─────────────────────────────┐
│┌───────────────────────────┐│
││       Physical Plan       ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            name           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         HASH_JOIN         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           INNER           │
│         sid = sid         ├──────────────┐
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │              │
│           EC: 1           │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│         SEQ_SCAN          ││           FILTER          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           exams           ││     prefix(name, 'Ma')    │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            sid            ││           EC: 1           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
│           EC: 3           ││                           │
└───────────────────────────┘└─────────────┬─────────────┘
                             ┌─────────────┴─────────────┐
                             │         SEQ_SCAN          │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │          students         │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │            sid            │
                             │            name           │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │ Filters: name>=Ma AND name│
                             │  <Mb AND name IS NOT NULL │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │           EC: 1           │
                             └───────────────────────────┘

```

--------------------------------

### Create table and insert initial values

Source: https://duckdb.org/docs/current/sql/statements/insert.html

Creates a table with a primary key and a unique column, then inserts an initial row. This is the setup for the subsequent ON CONFLICT examples.

```sql
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j INTEGER UNIQUE, k INTEGER);
INSERT INTO tbl
    VALUES (1, 20, 300);
SELECT * FROM tbl;
```

--------------------------------

### explain: Get query plan as string

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Call rel.explain() to get the query plan as a string. The example uses the same relation as the describe example.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.explain()

```

```text
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│        description        │
│           value           │
│     created_timestamp     │
│                           │
│          ~9 Rows          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           RANGE           │
│    ────────────────────   │
│      Function: RANGE      │
│                           │
│          ~9 Rows          │
└───────────────────────────┘

```

--------------------------------

### ST_X Example

Source: https://duckdb.org/docs/current/core_extensions/spatial/functions.html

Example of using ST_X to get the X coordinate of a point.

```sql
SELECT ST_X(ST_Point(1, 2))
```

--------------------------------

### Install Ibis with DuckDB backend using pip

Source: https://duckdb.org/docs/current/guides/python/ibis.html

Installs Ibis with the DuckDB backend and optional example datasets. The 'examples' extra is only required to access the sample data Ibis provides.

```bash
pip install 'ibis-framework[duckdb,examples]' # examples is only required to access the sample data Ibis provides

```

--------------------------------

### Show unixodbc_setup.sh help

Source: https://duckdb.org/docs/current/clients/odbc/linux.html

Displays the usage and options of the unixodbc_setup.sh script.

```bash
./unixodbc_setup.sh --help

```

--------------------------------

### Run the marimo SQL tutorial

Source: https://duckdb.org/docs/current/guides/python/marimo.html

Launch the marimo SQL tutorial from the terminal.

```bash
marimo tutorial sql
```

--------------------------------

### to_arrow_reader example

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Example of using to_arrow_reader with batch_size=1 to get a RecordBatchReader and read the next batch.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

pa_reader = rel.to_arrow_reader(batch_size=1)

pa_reader.read_next_batch()

```

--------------------------------

### Full Appender Example with Table Creation and Read Back

Source: https://duckdb.org/docs/current/clients/go/data_import.html

Complete example following the client's 'appender' example: creates a table via *sql.DB on the same connector, appends a row with the appender, closes it (which flushes), then reads the row back with a query. Demonstrates the recommended pattern for using the appender with a database/sql handle.

```go
package main

import (
    "context"
    "database/sql"
    "log"

    "github.com/duckdb/duckdb-go/v2"
)

func main() {
    c, err := duckdb.NewConnector("", nil)
    if err != nil {
        log.Fatal(err)
    }
    defer c.Close()

    con, err := c.Connect(context.Background())
    if err != nil {
        log.Fatal(err)
    }
    defer con.Close()

    db := sql.OpenDB(c)
    defer db.Close()
    if _, err := db.Exec(`CREATE TABLE users (name VARCHAR, age INTEGER)`); err != nil {
        log.Fatal(err)
    }

    a, err := duckdb.NewAppenderFromConn(con, "", "users")
    if err != nil {
        log.Fatal(err)
    }

    if err := a.AppendRow("Fred", int32(34)); err != nil {
        log.Fatal(err)
    }

    // Close flushes any buffered rows before closing the appender.
    if err := a.Close(); err != nil {
        log.Fatal(err)
    }

    var (
        name string
        age  int
    )
    row := db.QueryRowContext(context.Background(), `SELECT name, age FROM users`)
    if err := row.Scan(&name, &age); err != nil {
        log.Fatal(err)
    }
    log.Printf("User: name=%s, age=%d", name, age)
}
```

--------------------------------

### Install Specific DuckDB Version

Source: https://duckdb.org/docs/current/operations_manual/installing_duckdb/install_script

Installs a past DuckDB release (all the way back to v1.0.0) by setting the DUCKDB_VERSION variable. Example installs v1.2.2.

```bash
curl https://install.duckdb.org | DUCKDB_VERSION=1.2.2 bash

```

--------------------------------

### Example libpq Connection Strings

Source: https://duckdb.org/docs/current/core_extensions/postgres/overview.html

Examples of libpq connection strings: one with only the database name, another with host, port, database, and connect_timeout.

```text
dbname=postgresscanner

```

```text
host=localhost port=5432 dbname=mydb connect_timeout=10

```

--------------------------------

### Install extensions from build directory

Source: https://duckdb.org/docs/current/dev/building/building_extensions.html

Install all built extensions in one go by iterating over the extension directories in the build directory and running the INSTALL command for each.

```bash
# for release builds
cd build/release/extension/
# for debug builds
cd build/debug/extension/
# install extensions
for EXTENSION in *; do
    ../duckdb -c "INSTALL '${EXTENSION}/${EXTENSION}.duckdb_extension';"
done

```

--------------------------------

### Install supporting libraries (polars with pyarrow)

Source: https://duckdb.org/docs/current/guides/python/marimo.html

Install polars with pyarrow support, which is used in later examples for dataframes.

```bash
pip install "polars[pyarrow]" # or uv add "polars[pyarrow]"
```

--------------------------------

### generate_series(start, stop, step) example

Source: https://duckdb.org/docs/current/sql/functions/list.html

Creates a list from start (inclusive) to stop (inclusive) with a given step.

```sql
SELECT generate_series(2, 5, 3);
```

```text
[2, 5]
```

--------------------------------

### Create table and describe with DESCRIBE and SHOW

Source: https://duckdb.org/docs/current/guides/meta/describe.html

Creates a table with a primary key and then shows its schema using both DESCRIBE and SHOW, which are equivalent. The output includes columns for column_name, column_type, null, key, default, and extra.

```sql
CREATE TABLE tbl (i INTEGER PRIMARY KEY, j VARCHAR);
DESCRIBE tbl;
SHOW tbl; -- equivalent to DESCRIBE tbl;

```

--------------------------------

### Create a DataFrame for Expression Examples

Source: https://duckdb.org/docs/current/clients/python/expression

Sets up a pandas DataFrame with columns 'a', 'b', and 'c' used by the subsequent column expression examples.

```python
import duckdb
import pandas as pd

df = pd.DataFrame({
    'a': [1, 2, 3, 4],
    'b': [True, None, False, True],
    'c': [42, 21, 13, 14]
})

```

--------------------------------

### generate_series(start, stop) example

Source: https://duckdb.org/docs/current/sql/functions/list.html

Creates a list from start (inclusive) to stop (inclusive). Default step is 1.

```sql
SELECT generate_series(2, 5);
```

```text
[2, 3, 4, 5]
```

--------------------------------

### range(start, stop, step) example

Source: https://duckdb.org/docs/current/sql/functions/list.html

Creates a list from start (inclusive) to stop (exclusive) with a given step.

```sql
SELECT range(2, 5, 3);
```

```text
[2]
```

--------------------------------

### Install prerequisites with Homebrew

Source: https://duckdb.org/docs/current/dev/building/macos.html

Install the required build tools (git, cmake, ninja) using Homebrew.

```bash
brew install git cmake ninja
```

--------------------------------

### USE duck.main;

Source: https://duckdb.org/docs/current/sql/statements/use.html

Sets the 'duck.main' database and schema as the default.

```sql
USE duck.main;
```

--------------------------------

### Install Ibis using mamba

Source: https://duckdb.org/docs/current/guides/python/ibis.html

Installs the Ibis framework via mamba. This does not include the DuckDB backend or examples extras.

```bash
mamba install ibis-framework

```

--------------------------------

### Install Ibis using conda

Source: https://duckdb.org/docs/current/guides/python/ibis.html

Installs the Ibis framework via conda. This does not include the DuckDB backend or examples extras.

```bash
conda install ibis-framework

```

--------------------------------

### odbc_connect example with UID and PWD in string

Source: https://duckdb.org/docs/current/core_extensions/odbc/functions.html

Example connecting with credentials embedded in the connection string.

```sql
SET VARIABLE conn = odbc_connect('Driver={Oracle Driver};DBQ=//127.0.0.1:1521/XE;UID=scott;PWD=tiger')

```

--------------------------------

### DatabaseInit

Source: https://duckdb.org/docs/current/clients/adbc.html

Finishes setting options and initializes the database.

```APIDOC
## DatabaseInit

### Description
Finishes setting options and initializes the database.

### Function Signature
`AdbcDatabaseInit(AdbcDatabase *database, AdbcError *error)`

### Parameters
- **database** (AdbcDatabase*) - Required - Pointer to the database object.
- **error** (AdbcError*) - Required - Pointer to an error object for error reporting.

### Example
```c
AdbcDatabaseInit(&adbc_database, &adbc_error);
```
```

--------------------------------

### Sample .help output for .maxrows, .maxwidth, and .mode

Source: https://duckdb.org/docs/current/clients/cli/dot_commands.html

Shows the help text for the .maxrows, .maxwidth, and .mode commands, including their default values and usage notes.

```text
.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

```

--------------------------------

### Update using joins - setup tables

Source: https://duckdb.org/docs/current/sql/statements/update.html

Creates the city and country tables and inserts sample data.

```sql
CREATE TABLE city (name VARCHAR, revenue BIGINT, country_code VARCHAR);
CREATE TABLE country (code VARCHAR, name VARCHAR);
INSERT INTO city VALUES ('Paris', 700, 'FR'), ('Lyon', 200, 'FR'), ('Brussels', 400, 'BE');
INSERT INTO country VALUES ('FR', 'France'), ('BE', 'Belgium');

```

--------------------------------

### Install and load the ICU extension

Source: https://duckdb.org/docs/current/core_extensions/icu.html

Manually install and load the ICU extension. The extension is normally autoloaded on first use, but this explicit setup is available if needed.

```sql
INSTALL icu;
LOAD icu;

```

--------------------------------

### macOS Installation Warning Output

Source: https://duckdb.org/docs/current/clients/r.html

Example warning message shown when installing DuckDB on macOS, indicating an issue loading the R_X11.so shared object. This is just a warning and can be ignored.

```text
Warning message:
In doTryCatch(return(expr), name, parentenv, handler) :
  unable to load shared object '/Library/Frameworks/R.framework/Resources/modules//R_X11.so':
  dlopen(/Library/Frameworks/R.framework/Resources/modules//R_X11.so, 0x0006): Library not loaded: /opt/X11/lib/libSM.6.dylib
  Referenced from: <31EADEB5-0A17-3546-9944-9B3747071FE8> /Library/Frameworks/R.framework/Versions/4.4-arm64/Resources/modules/R_X11.so
  Reason: tried: '/opt/X11/lib/libSM.6.dylib' (no such file) ...
> ')

```

--------------------------------

### Update from other table - setup and initial SELECT

Source: https://duckdb.org/docs/current/sql/statements/update.html

Creates the original and new tables, and selects from original to show the initial state.

```sql
CREATE OR REPLACE TABLE original AS
    SELECT 1 AS key, 'original value' AS value
    UNION ALL
    SELECT 2 AS key, 'original value 2' AS value;

CREATE OR REPLACE TABLE new AS
    SELECT 1 AS key, 'new value' AS value
    UNION ALL
    SELECT 2 AS key, 'new value 2' AS value;

SELECT *
FROM original;

```

--------------------------------

### dtypes: Return list of column types

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Call rel.dtypes (alias: types) to get a list of the column types of the relation. The example uses the same relation as the describe example.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.dtypes

```

```text
 [UUID, VARCHAR, BIGINT, TIMESTAMP WITH TIME ZONE]

```

--------------------------------

### Install UCRT64 GCC toolchain with pacman

Source: https://duckdb.org/docs/current/clients/go/troubleshoot.html

Install the UCRT64 GCC toolchain in an MSYS2 shell to get a compatible gcc and runtime libraries for building the Go client on Windows.

```bash
pacman -S mingw-w64-ucrt-x86_64-gcc

```

--------------------------------

### Basic querying with fetchall and fetchone

Source: https://duckdb.org/docs/current/clients/python/dbapi.html

Shows how to create a table, insert rows, and retrieve results using fetchall and fetchone. fetchall returns all rows and completes the transaction; fetchone returns one row at a time and closes the transaction when no more rows remain.

```python
# create a table
con.execute("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)")
# insert two items into the table
con.execute("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)")

# retrieve the items again
con.execute("SELECT * FROM items")
print(con.fetchall())
# [('jeans', Decimal('20.00'), 1), ('hammer', Decimal('42.20'), 2)]

# retrieve the items one at a time
con.execute("SELECT * FROM items")
print(con.fetchone())
# ('jeans', Decimal('20.00'), 1)
print(con.fetchone())
# ('hammer', Decimal('42.20'), 2)
print(con.fetchone()) # This closes the transaction. Any subsequent calls to .fetchone will return None
# None

```

--------------------------------

### Install and load unity_catalog extension

Source: https://duckdb.org/docs/current/core_extensions/unity_catalog.html

Installs and loads the unity_catalog extension. Both extensions are only supported on given platforms.

```sql
INSTALL unity_catalog;
LOAD unity_catalog;

```

--------------------------------

### FORCE INSTALL commands generated by UPDATE EXTENSIONS

Source: https://duckdb.org/docs/current/sql/statements/update_extensions

When a change is found for an extension, DuckDB executes FORCE INSTALL for each changed extension. This example shows the commands generated when name_a and name_c changed.

```sql
FORCE INSTALL name_a;
FORCE INSTALL name_c;

```

--------------------------------

### alias: Get current alias of relation

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Gets the name of the current alias of a relation. The example creates a relation from a SQL query and accesses the alias property. The output is an unnamed relation identifier.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.alias

```

```text
unnamed_relation_43c808c247431be5

```

--------------------------------

### Install prerequisites and build DuckDB CLI on Ubuntu/Debian

Source: https://duckdb.org/docs/current/dev/building/linux.html

Use apt-get to install required packages (git, g++, cmake, ninja-build, libssl-dev, libcurl4-openssl-dev), then clone and build DuckDB with Ninja.

```bash
sudo apt-get update
sudo apt-get install -y git g++ cmake ninja-build libssl-dev libcurl4-openssl-dev
git clone https://github.com/duckdb/duckdb
cd duckdb
GEN=ninja make

```

--------------------------------

### Start the UI from the command line

Source: https://duckdb.org/docs/current/core_extensions/ui.html

Run `duckdb -ui` to start the UI in your default browser. The UI connects to the DuckDB instance it was started from, so any data already loaded will be available.

```bash
duckdb -ui

```

--------------------------------

### description: Return column metadata of the result

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Call rel.description to get a list of tuples with column metadata (name, type, and None placeholders for other fields). The example uses the same relation as the describe example.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.description

```

```text
[('id', 'UUID', None, None, None, None, None),
 ('description', 'STRING', None, None, None, None, None),
 ('value', 'NUMBER', None, None, None, None, None),
 ('created_timestamp', 'DATETIME', None, None, None, None, None)]  

```

--------------------------------

### any_value example

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Example usage of the any_value method on a DuckDB relation. It creates a relation with generated UUIDs, descriptions, values, and timestamps, then calls any_value('id') to get the first non-null value from the id column.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.any_value('id')
```

--------------------------------

### Install the sqlite extension

Source: https://duckdb.org/docs/current/guides/database_integration/sqlite.html

Run once to install the sqlite extension.

```sql
INSTALL sqlite;

```

--------------------------------

### Install Supporting Libraries

Source: https://duckdb.org/docs/current/guides/python/jupyter.html

Install jupysql, pandas, matplotlib, and duckdb-engine for enhanced DuckDB notebook experience.

```bash
pip install jupysql pandas matplotlib duckdb-engine

```

--------------------------------

### Generate TPC-H lineitem table

Source: https://duckdb.org/docs/current/guides/meta/summarize.html

Install and load the tpch extension, then generate the SF1 dataset to use in the SUMMARIZE example.

```sql
INSTALL tpch;
LOAD tpch;
CALL dbgen(sf = 1);

```

--------------------------------

### describe: Get basic statistics for each column

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Call rel.describe() to get basic statistics (min, max, count, mean, stddev, median) and NULL existence for each column of the relation. The example creates a relation with a SQL query and prints the result.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.describe()

```

```text
┌─────────┬──────────────────────────────────────┬─────────────────┬────────────────────┬────────────────────────────┐
│  aggr   │                  id                  │   description   │       value        │     created_timestamp      │
│ varchar │               varchar                │     varchar     │       double       │          varchar           │
├─────────┼──────────────────────────────────────┼─────────────────┼────────────────────┼────────────────────────────┤
│ count   │ 9                                    │ 9               │                9.0 │ 9                          │
│ mean    │ NULL                                 │ NULL            │                5.0 │ NULL                       │
│ stddev  │ NULL                                 │ NULL            │ 2.7386127875258306 │ NULL                       │
│ min     │ 08fdcbf8-4e53-4290-9e81-423af263b518 │ value is even   │                1.0 │ 2025-04-09 15:41:20.642+02 │
│ max     │ fb10390e-fad5-4694-91cb-e82728cb6f9f │ value is uneven │                9.0 │ 2025-04-09 15:49:20.642+02 │
│ median  │ NULL                                 │ NULL            │                5.0 │ NULL                       │
└─────────┴──────────────────────────────────────┴─────────────────┴────────────────────┴────────────────────────────┘ 

```

--------------------------------

### product example

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Example usage of the product method with a column, groups, and projected columns.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.product(column="value", groups="description", projected_columns="description")

```

--------------------------------

### generate_series(stop) example

Source: https://duckdb.org/docs/current/sql/functions/list.html

Creates a list from 0 (inclusive) to stop (inclusive). Default start is 0, default step is 1.

```sql
SELECT generate_series(5);
```

```text
[0, 1, 2, 3, 4, 5]
```

--------------------------------

### ST_Drivers example

Source: https://duckdb.org/docs/current/core_extensions/spatial/functions.html

Example query to list all supported GDAL drivers and file formats.

```sql
SELECT * FROM ST_Drivers();
```

--------------------------------

### Set EXPLAIN output to all

Source: https://duckdb.org/docs/current/configuration/pragmas.html

Configures EXPLAIN to show all query plans.

```sql
SET explain_output = 'all';

```

--------------------------------

### range(stop) example

Source: https://duckdb.org/docs/current/sql/functions/list.html

Creates a list from 0 (inclusive) to stop (exclusive). Default start is 0, default step is 1.

```sql
SELECT range(5);
```

```text
[0, 1, 2, 3, 4]
```

--------------------------------

### Start the UI from SQL

Source: https://duckdb.org/docs/current/core_extensions/ui.html

Run `CALL start_ui();` to start the UI from SQL. This opens the UI in your default browser.

```sql
CALL start_ui();

```

--------------------------------

### Run isql with DuckDB DSN

Source: https://duckdb.org/docs/current/clients/odbc/macos.html

Starts the unixODBC command-line client isql using the DSN defined in odbc.ini to validate the installation.

```bash
isql DuckDB

```

--------------------------------

### unique: Get distinct values in a column

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Returns the distinct values in a column. The example retrieves distinct values from the 'description' column.

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.unique(unique_aggr="description")
```

```text
┌─────────────────┐
│   description   │
│     varchar     │
├─────────────────┤
│ value is even   │
│ value is uneven │
└─────────────────┘
```

--------------------------------

### PRAGMA explain_output = 'all'

Source: https://duckdb.org/docs/current/guides/meta/explain.html

Sets the EXPLAIN output to show both the physical and optimized plans.

```sql
PRAGMA explain_output = 'all';

```

--------------------------------

### select_types example

Source: https://duckdb.org/docs/current/clients/python/relational_api.html

Alias of select_dtypes, selects columns of type VARCHAR and returns distinct values. Uses the same relation setup and types=[duckdb.sqltypes.VARCHAR].

```python
import duckdb

duckdb_conn = duckdb.connect()

rel = duckdb_conn.sql("""
        select 
            gen_random_uuid() as id, 
            concat('value is ', case when mod(range,2)=0 then 'even' else 'uneven' end) as description,
            range as value, 
            now() + concat(range,' ', 'minutes')::interval as created_timestamp
        from range(1, 10)
    """
)

rel.select_types(types=[duckdb.sqltypes.VARCHAR]).distinct()

```

```text
┌─────────────────┐
│   description   │
│     varchar     │
├─────────────────┤
│ value is even   │
│ value is uneven │
└─────────────────┘

```

--------------------------------

### odbc_connect example with positional username and password

Source: https://duckdb.org/docs/current/core_extensions/odbc/functions.html

Example connecting with username and password passed as positional parameters.

```sql
SET VARIABLE conn = odbc_connect('Driver={Oracle Driver};DBQ=//127.0.0.1:1521/XE', 'scott', 'tiger')

```

--------------------------------

### dayofyear(date)

Source: https://duckdb.org/docs/current/sql/functions/datepart.html

Extracts the day of the year from a date, starting from 1 (January 1 = 1). For example, dayofyear(DATE '1992-02-15') returns 46.

```APIDOC
## dayofyear(date)

### Description
Extracts the day of the year from a date, starting from 1 (January 1 = 1).

### Parameters
- **date** (DATE) - The date from which to extract the day of the year.

### Returns
- **dayofyear** (INTEGER) - The day of the year (1-366).

### Example
```sql
dayofyear(DATE '1992-02-15')
```
**Result:** `46`
```

--------------------------------

### USE memory;

Source: https://duckdb.org/docs/current/sql/statements/use.html

Sets the 'memory' database as the default. Will use 'main' schema implicitly or error if it does not exist.

```sql
USE memory;
```

--------------------------------

### JSONPath extraction example

Source: https://duckdb.org/docs/current/data/json/json_functions.html

Shows how to extract an element using JSONPath syntax, which uses '.' for fields, '[i]' for array elements, and starts with '$'. The query returns 1.

```sql
SELECT json_extract('{"duck": [1, 2, 3]}', '$.duck[0]');
```

```text
1
```

--------------------------------

### Install postgres extension

Source: https://duckdb.org/docs/current/guides/database_integration/postgres.html

Run this SQL command once to install the postgres extension.

```sql
INSTALL postgres;

```
Tokens:
Raw