Skip to content

motherduckdb/agent-skills / plugins/motherduck-skills-claude/skills/motherduck-duckdb-sql

motherduck-duckdb-sql

Verified

Look up or repair DuckDB SQL syntax and verify MotherDuck-specific command and feature support.

FileKindSize
SKILL.mdskill md3.8 KB
references/SYNTAX_REFERENCE.mdmarkdown18.6 KB

references/SYNTAX_REFERENCE.md

View on GitHub
# DuckDB SQL Syntax Reference

Complete function and data type reference for DuckDB on MotherDuck.

## Contents

- [Version-Sensitive Features](#version-sensitive-features)
- [Data Types](#data-types)
- [String Functions](#string-functions)
- [Numeric Functions](#numeric-functions)
- [Date/Time Functions](#datetime-functions)
- [Aggregate Functions](#aggregate-functions)
- [Window Functions](#window-functions)
- [JSON Functions](#json-functions)
- [List Functions](#list-functions)
- [Spatial Functions](#spatial-functions)
- [H3 Functions](#h3-functions)
- [Table Functions](#table-functions)
- [PIVOT / UNPIVOT](#pivot--unpivot)
- [Regular Expressions](#regular-expressions)
- [Conditional Expressions](#conditional-expressions)
- [Common Table Expressions (CTEs)](#common-table-expressions-ctes)
- [CREATE TABLE, INSERT, COPY](#create-table-insert-copy)
- [Useful Patterns](#useful-patterns)

---

## Version-Sensitive Features

DuckDB's current upstream documentation can move ahead of the DuckDB versions currently supported by MotherDuck. Check MotherDuck's version-lifecycle docs before treating newly released syntax or types as production-safe in MotherDuck.

Examples to verify before relying on them:

- `MERGE INTO`
- `FILL()` window interpolation
- newly added or newly expanded types such as `VARIANT` or native `GEOMETRY`
- changed date/time behavior in recent DuckDB releases, such as `date_trunc` returning `TIMESTAMP` when applied to `DATE`
- lakehouse-format changes that depend on current DuckDB, DuckLake, Iceberg, Delta, or httpfs extension behavior

MotherDuck-only lifecycle commands are operational SQL, not analytical query syntax:

```sql
SHUTDOWN;
SHUTDOWN TERMINATE (REASON 'stuck batch job');
```

Use `SHUTDOWN` for graceful Duckling shutdown after current work completes. Use `SHUTDOWN TERMINATE` only when the user explicitly wants to interrupt running work.

MotherDuck recovery commands are also operational SQL. Verify current snapshot retention and plan limits before promising a recovery window.

```sql
CREATE SNAPSHOT named_snapshot OF my_database;

CREATE DATABASE restored_database FROM my_database (
    SNAPSHOT_NAME 'named_snapshot'
);

ALTER DATABASE my_database SET SNAPSHOT TO (
    SNAPSHOT_NAME 'named_snapshot'
);

UNDROP DATABASE dropped_database;
```

## Data Types

| Type | Description | Example |
|------|-------------|---------|
| `BOOLEAN` | True/false | `TRUE`, `FALSE` |
| `TINYINT` | 8-bit integer (-128 to 127) | `42::TINYINT` |
| `SMALLINT` | 16-bit integer | `1000::SMALLINT` |
| `INTEGER` / `INT` | 32-bit integer | `42` |
| `BIGINT` | 64-bit integer | `9999999999` |
| `HUGEINT` | 128-bit integer | `170141183460469231731687303715884105727` |
| `FLOAT` / `REAL` | 32-bit floating point | `3.14::FLOAT` |
| `DOUBLE` | 64-bit floating point | `3.14159265358979` |
| `DECIMAL(p, s)` | Fixed-point decimal | `DECIMAL(18, 2)` |
| `VARCHAR` / `TEXT` | Variable-length string | `'hello'` |
| `BLOB` | Binary data | `'\xAA\xBB'::BLOB` |
| `DATE` | Calendar date | `DATE '2023-07-23'` |
| `TIME` | Time of day | `TIME '14:30:00'` |
| `TIMESTAMP` | Date and time (no timezone) | `TIMESTAMP '2023-07-23 14:30:00'` |
| `TIMESTAMPTZ` | Timestamp with timezone | `TIMESTAMPTZ '2023-07-23 14:30:00+00'` |
| `INTERVAL` | Time duration | `INTERVAL 30 DAY` |
| `UUID` | Universally unique identifier | `gen_random_uuid()` |
| `JSON` | JSON data | `'{"a": 1}'::JSON` |
| `LIST(T)` / `T[]` | Variable-length list | `[1, 2, 3]` |
| `STRUCT` | Named fields | `{'a': 1, 'b': 'text'}` |
| `MAP(K, V)` | Key-value pairs | `MAP(['a'], [1])` |
| `UNION(...)` | Tagged union | `UNION(num INT, str VARCHAR)` |
| `ENUM(...)` | Enumeration | `CREATE TYPE mood AS ENUM ('happy', 'sad')` |

---

## String Functions

```sql
length('hello')                        -- 5
upper('hello') / lower('HELLO')        -- 'HELLO' / 'hello'
trim('  hi  ') / ltrim() / rtrim()     -- 'hi'
substr('DuckDB', 1, 4)                -- 'Duck' (start, length)
'DuckDB'[1:4]                         -- 'Duck' (slice, 1-indexed)
left('DuckDB', 4) / right('DuckDB', 2) -- 'Duck' / 'DB'
replace('DuckDB', 'Duck', 'Goose')    -- 'GooseDB'
contains('DuckDB', 'Duck')            -- true
starts_with('DuckDB', 'Duck')         -- true
ends_with('DuckDB', 'DB')             -- true
concat('a', ' ', 'b') / 'a' || 'b'   -- Concatenation
format('{} has {} items', 'cart', 5)   -- 'cart has 5 items'
lpad('42', 5, '0')                    -- '00042'
rpad('hi', 5, '!')                    -- 'hi!!!'
repeat('ab', 3)                       -- 'ababab'
reverse('hello')                      -- 'olleh'
split_part('a.b.c', '.', 2)           -- 'b'
string_split('a,b,c', ',')            -- ['a', 'b', 'c']
string_agg(col, ', ' ORDER BY col)    -- Ordered concatenation
regexp_replace('abc123', '[0-9]+', 'X') -- 'abcX'
regexp_matches('abc123', '[0-9]+')     -- true
regexp_extract('abc123def', '([0-9]+)', 1) -- '123'
```

---

## Numeric Functions

```sql
abs(-42)                               -- 42
ceil(3.2) / floor(3.8)                -- 4 / 3
round(3.14159, 2)                      -- 3.14
trunc(3.99)                            -- 3
ln(e) / log10(1000) / log2(8)         -- 1.0 / 3.0 / 3.0
power(2, 10)                           -- 1024
sqrt(144)                              -- 12.0
mod(17, 5) / 17 % 5                   -- 2
sign(-42)                              -- -1
greatest(1, 5, 3) / least(1, 5, 3)   -- 5 / 1
random()                               -- Random float in [0, 1)
setseed(0.42)                          -- Set seed for reproducibility
```

---

## Date/Time Functions

```sql
now() / current_date / current_timestamp                   -- Current date/time
date_part('year', DATE '2023-07-23')                       -- 2023
EXTRACT(MONTH FROM DATE '2023-07-23')                      -- 7
date_diff('day', DATE '2023-01-01', DATE '2023-07-23')    -- 203
age(TIMESTAMP '2023-07-23', TIMESTAMP '2020-01-01')       -- 3 years 6 months 22 days
date_add(DATE '2023-07-23', INTERVAL 30 DAY)              -- 2023-08-22
date_sub(DATE '2023-07-23', INTERVAL 1 MONTH)             -- 2023-06-23
date_trunc('month', TIMESTAMP '2023-07-23 14:30:00')      -- 2023-07-01 00:00:00
make_date(2023, 7, 23)                                     -- DATE '2023-07-23'
make_timestamp(2023, 7, 23, 14, 30, 0)                     -- TIMESTAMP
strftime(NOW(), '%Y-%m-%d')                                -- '2023-07-23'
strptime('07/23/2023', '%m/%d/%Y')::DATE                   -- Parse custom format
epoch(TIMESTAMP '2023-07-23 00:00:00')                     -- Seconds since epoch
epoch_ms(TIMESTAMP '2023-07-23 00:00:00')                  -- Milliseconds since epoch
to_timestamp(1690070400)                                    -- Epoch seconds to timestamp
```

---

## Aggregate Functions

```sql
-- Basic aggregates
count(*)                               -- Row count
count(DISTINCT col)                    -- Distinct count
sum(amount)                            -- Total
avg(score)                             -- Average
min(price)                             -- Minimum
max(price)                             -- Maximum

-- First/last (order-dependent)
first(col)                             -- First value encountered
last(col)                              -- Last value encountered
first(col ORDER BY date_col)           -- First by explicit order

-- Argmax/argmin — value at row where another column is extremal
arg_max(status, updated_at)            -- Status at latest update
arg_min(product_name, price)           -- Name of cheapest product

-- List aggregate — collect values into a list
list(col)                              -- [val1, val2, ...]
list(DISTINCT col)                     -- Deduplicated list

-- String aggregate
string_agg(name, ', ' ORDER BY name)   -- 'Alice, Bob, Charlie'

-- Approximate
approx_count_distinct(col)             -- HyperLogLog distinct count

-- Statistical
median(col)                            -- Median value
mode(col)                              -- Most frequent value
quantile(col, 0.95)                    -- 95th percentile (discrete)
quantile_cont(col, 0.95)              -- 95th percentile (continuous/interpolated)
quantile_disc(col, 0.95)              -- 95th percentile (discrete)
stddev(col)                            -- Standard deviation (sample)
variance(col)                          -- Variance (sample)

-- Correlation and regression
corr(x, y)                             -- Pearson correlation
covar_pop(x, y)                        -- Population covariance
covar_samp(x, y)                       -- Sample covariance
regr_slope(y, x)                       -- Linear regression slope
regr_intercept(y, x)                   -- Linear regression intercept

-- Bitwise
bit_and(col)                           -- Bitwise AND of all values
bit_or(col)                            -- Bitwise OR of all values
bit_xor(col)                           -- Bitwise XOR of all values

-- Boolean
bool_and(col)                          -- TRUE if all values are TRUE
bool_or(col)                           -- TRUE if any value is TRUE
```

---

## Window Functions

```sql
ROW_NUMBER() OVER (ORDER BY score DESC)
RANK() OVER (ORDER BY score DESC)              -- Gaps on ties
DENSE_RANK() OVER (ORDER BY score DESC)        -- No gaps
NTILE(4) OVER (ORDER BY score)                 -- Quartile buckets
PERCENT_RANK() OVER (ORDER BY score)           -- Relative rank (0 to 1)
CUME_DIST() OVER (ORDER BY score)              -- Cumulative distribution
LAG(col, 1) OVER (ORDER BY date_col)           -- Previous row
LEAD(col, 1) OVER (ORDER BY date_col)          -- Next row
FIRST_VALUE(col) OVER (PARTITION BY grp ORDER BY date_col)
LAST_VALUE(col) OVER (PARTITION BY grp ORDER BY date_col
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
NTH_VALUE(col, 3) OVER (ORDER BY date_col)     -- Nth row value
-- Window frames
SUM(amount) OVER (ORDER BY date_col ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
```

---

## JSON Functions

Requires the `json` extension (pre-installed on MotherDuck).

```sql
json_col->>'key'                              -- Extract as text
json_col->'$.nested.path'                     -- Extract as JSON
json_extract(data, '$.user.name')             -- Extract as JSON
json_extract_string(data, '$.user.name')      -- Extract as VARCHAR
json_type(data)                               -- 'OBJECT', 'ARRAY', etc.
json_array_length(data->'$.items')            -- Array element count
json_keys(data)                               -- Top-level keys
json_valid('{"a": 1}')                        -- true
json_serialize(any_value)                     -- Value to JSON string
to_json({'a': 1, 'b': 'text'})               -- Struct to JSON
from_json('{"a": 1}', '{"a": "INT"}')        -- JSON to typed struct
```

---

## List Functions

```sql
-- Construction
list_value(1, 2, 3)                           -- [1, 2, 3]
[1, 2, 3]                                     -- Literal syntax
generate_series(1, 10)                         -- [1, 2, ..., 10] as rows
range(0, 5)                                    -- [0, 1, 2, 3, 4] as rows

-- Aggregation and transformation
list_aggregate([1, 2, 3], 'sum')              -- 6
list_sort([3, 1, 2])                          -- [1, 2, 3]
list_reverse_sort([3, 1, 2])                  -- [3, 2, 1]
list_distinct([1, 1, 2, 3])                   -- [1, 2, 3]
list_unique([1, 1, 2, 3])                     -- 3 (count of unique)

-- Search
list_contains([1, 2, 3], 2)                   -- true
list_position([10, 20, 30], 20)               -- 2 (1-indexed)

-- Higher-order functions
list_filter([1, 2, 3, 4, 5], x -> x > 3)     -- [4, 5]
list_transform([1, 2, 3], x -> x * 10)        -- [10, 20, 30]
list_reduce([1, 2, 3, 4], (x, y) -> x + y)   -- 10

-- Manipulation
list_concat([1, 2], [3, 4])                   -- [1, 2, 3, 4]
list_slice([10, 20, 30, 40], 2, 3)            -- [20, 30]
flatten([[1, 2], [3, 4]])                     -- [1, 2, 3, 4]

-- Expansion
UNNEST([10, 20, 30])                          -- Expands to 3 rows

-- List comprehensions
[x * 2 FOR x IN [1, 2, 3]]                   -- [2, 4, 6]
[x FOR x IN [1, 2, 3, 4, 5] IF x > 3]       -- [4, 5]
```

---

## Spatial Functions

Provided by the `spatial` extension (pre-installed on MotherDuck).

```sql
-- Construction
ST_Point(longitude, latitude)
ST_MakeLine(geom1, geom2)
ST_GeomFromText('POINT(0 0)')
ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')

-- Measurement
ST_Area(polygon)                              -- Area of polygon
ST_Length(line)                                -- Length of line
ST_Distance(geom1, geom2)                     -- Distance between geometries

-- Relationships
ST_Intersects(geom1, geom2)                   -- True if geometries intersect
ST_Contains(outer_geom, inner_geom)           -- True if outer contains inner
ST_Within(inner_geom, outer_geom)             -- True if inner is within outer

-- Transformation
ST_Transform(geom, 'EPSG:4326', 'EPSG:3857') -- Reproject coordinates
ST_Buffer(geom, distance)                     -- Buffer around geometry

-- Serialization
ST_AsText(geom)                               -- WKT string
ST_AsGeoJSON(geom)                            -- GeoJSON string
```

---

## H3 Functions

Provided by the `h3` extension (pre-installed on MotherDuck).

```sql
-- Indexing
h3_latlng_to_cell(lat, lng, resolution)       -- Lat/lng to H3 cell ID
h3_cell_to_latlng(cell_id)                    -- H3 cell to lat/lng center
h3_cell_to_boundary(cell_id)                  -- H3 cell boundary polygon

-- Hierarchy
h3_get_resolution(cell_id)                    -- Resolution of cell (0-15)
h3_cell_to_parent(cell_id, parent_res)        -- Parent cell at resolution
h3_cell_to_children(cell_id, child_res)       -- Child cells at resolution

-- Traversal and area
h3_grid_disk(cell_id, k)                      -- Cells within k rings
h3_cell_area(cell_id, 'km^2')                 -- Area of cell

-- Conversion
h3_cells_to_multi_polygon(cell_list)          -- Cells to polygon geometry
```

---

## Table Functions

```sql
-- CSV
SELECT * FROM read_csv('data.csv');
SELECT * FROM read_csv('data.csv',
    header = true,
    delim = ',',
    quote = '"',
    columns = {'name': 'VARCHAR', 'age': 'INTEGER'}
);

-- Parquet
SELECT * FROM read_parquet('data.parquet');
SELECT * FROM read_parquet('s3://bucket/path/*.parquet');  -- Glob pattern
SELECT * FROM read_parquet(['file1.parquet', 'file2.parquet']);

-- JSON
SELECT * FROM read_json('data.json');
SELECT * FROM read_json('data.json', format = 'array');

-- Excel
SELECT * FROM read_excel('data.xlsx');
SELECT * FROM read_excel('data.xlsx', sheet = 'Sheet2');

-- Generate series and range
SELECT * FROM generate_series(1, 100);         -- 1 to 100 inclusive
SELECT * FROM range(0, 10);                    -- 0 to 9

-- Unnest (expand list/struct to rows)
SELECT UNNEST([1, 2, 3]) AS val;
SELECT UNNEST({'a': 1, 'b': 2});               -- Columns a and b

-- Glob (list files matching pattern)
SELECT * FROM glob('data/*.csv');
```

---

## PIVOT / UNPIVOT

### PIVOT — rows to columns

```sql
PIVOT monthly_sales ON month USING SUM(revenue) GROUP BY product;
PIVOT orders ON status IN ('pending', 'shipped', 'delivered') USING COUNT(*) GROUP BY region;
```

### UNPIVOT — columns to rows

```sql
UNPIVOT quarterly_data ON q1, q2, q3, q4 INTO NAME quarter VALUE revenue;
```

---

## Regular Expressions

```sql
-- SIMILAR TO (SQL standard regex)
SELECT 'hello' SIMILAR TO 'h.*o';             -- true
SELECT name FROM t WHERE name SIMILAR TO '[A-Z]%';

-- Regex match (boolean)
regexp_matches('abc123', '^[a-z]+[0-9]+$')    -- true

-- Regex replace
regexp_replace('abc 123 def', '[0-9]+', 'NUM') -- 'abc NUM def'
regexp_replace('aaa', 'a', 'b', 'g')          -- 'bbb' (global flag)

-- Regex extract
regexp_extract('email: user@host.com', '([a-z]+)@([a-z.]+)', 0) -- 'user@host.com'
regexp_extract('email: user@host.com', '([a-z]+)@([a-z.]+)', 1) -- 'user'
regexp_extract('email: user@host.com', '([a-z]+)@([a-z.]+)', 2) -- 'host.com'

-- Split by regex
regexp_split_to_array('one, two,  three', ',\\s*') -- ['one', 'two', 'three']

-- LIKE and ILIKE (pattern matching, not regex)
SELECT * FROM t WHERE name LIKE 'A%';         -- Case-sensitive
SELECT * FROM t WHERE name ILIKE 'a%';        -- Case-insensitive
```

---

## Conditional Expressions

```sql
CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END
COALESCE(col1, col2, 'default')               -- First non-NULL
IFNULL(nullable_col, 'fallback')              -- Two-arg coalesce
NULLIF(col, 0)                                -- NULL if col = 0
IIF(score > 50, 'pass', 'fail')               -- Inline if (ternary)
```

---

## Common Table Expressions (CTEs)

```sql
-- Basic CTE
WITH active AS (SELECT * FROM users WHERE status = 'active')
SELECT department, COUNT(*) FROM active GROUP BY ALL;

-- Multiple CTEs
WITH
  recent AS (SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL 30 DAY),
  summary AS (SELECT customer_id, SUM(amount) AS total FROM recent GROUP BY customer_id)
SELECT u.name, s.total FROM users u JOIN summary s ON u.id = s.customer_id;

-- Recursive CTE
WITH RECURSIVE counter(n) AS (
    SELECT 1 UNION ALL SELECT n + 1 FROM counter WHERE n < 10
)
SELECT * FROM counter;
```

---

## CREATE TABLE, INSERT, COPY

```sql
-- Standard CREATE
CREATE TABLE events (
    id INTEGER PRIMARY KEY, name VARCHAR NOT NULL,
    created_at TIMESTAMP DEFAULT current_timestamp, tags VARCHAR[], metadata JSON
);

-- CTAS
CREATE TABLE summary AS SELECT category, SUM(sales) AS total FROM raw_data GROUP BY ALL;

-- CREATE OR REPLACE
CREATE OR REPLACE TABLE staging AS SELECT * FROM read_parquet('s3://bucket/*.parquet');

-- INSERT
INSERT INTO events (name, tags) VALUES ('click', ['ui', 'button']);
INSERT INTO archive SELECT * FROM events WHERE created_at < '2023-01-01';

-- COPY (export)
COPY (SELECT * FROM events) TO 'events.parquet' (FORMAT PARQUET);
COPY (SELECT * FROM events) TO 'events.csv' (HEADER, DELIMITER ',');
```

---

## Useful Patterns

### Deduplication with QUALIFY

```sql
SELECT *
FROM raw_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY ingested_at DESC) = 1;
```

### Lateral Join (correlated subquery as table)

```sql
SELECT o.order_id, t.item
FROM orders o, LATERAL UNNEST(o.items) AS t(item);
```

### Sampling

```sql
SELECT * FROM large_table USING SAMPLE 1000;          -- 1000 rows
SELECT * FROM large_table USING SAMPLE 10 PERCENT;    -- 10% of rows
```

### String aggregation with ordering

```sql
SELECT department, string_agg(name, ', ' ORDER BY name) AS members
FROM employees
GROUP BY department;
```

### Generate date series for gap-filling

```sql
WITH dates AS (
    SELECT UNNEST(generate_series(DATE '2023-01-01', DATE '2023-12-31', INTERVAL 1 DAY)) AS dt
)
SELECT d.dt, COALESCE(e.count, 0) AS event_count
FROM dates d
LEFT JOIN (SELECT date_trunc('day', created_at) AS dt, COUNT(*) AS count FROM events GROUP BY 1) e
ON d.dt = e.dt;
```
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall