DuckDB Data Types: Complete Reference Guide

DuckDB data types reference: integers, floats, decimals, strings, dates, timestamps, JSON, arrays, structs, and more. With examples and casting patterns.

Mark Rachapoom

March 26, 2026·7 min read

DuckDB Data Types: Complete Reference Guide

DuckDB has a rich type system that covers everything from standard SQL types to nested structures, arrays, and enumerations. Understanding which type to use for each column affects storage efficiency, query performance, and correctness.

This is the reference guide you'll want bookmarked.

Numeric Types#

Integer Types#

Type	Aliases	Size	Range
`TINYINT`	`INT1`	1 byte	-128 to 127
`SMALLINT`	`INT2`, `SHORT`	2 bytes	-32,768 to 32,767
`INTEGER`	`INT`, `INT4`	4 bytes	-2.1B to 2.1B
`BIGINT`	`INT8`, `LONG`	8 bytes	-9.2 quintillion to 9.2 quintillion
`HUGEINT`		16 bytes	±170 undecillion
`UTINYINT`		1 byte	0 to 255
`USMALLINT`		2 bytes	0 to 65,535
`UINTEGER`		4 bytes	0 to 4.3B
`UBIGINT`		8 bytes	0 to 18.4 quintillion

CREATE TABLE metrics (
    id BIGINT,
    count INTEGER,
    small_flag TINYINT,
    large_value HUGEINT
);

Use INTEGER for most row counts. Use BIGINT for IDs in large systems. Use HUGEINT for astronomical calculations or cryptographic hashes.

Floating Point Types#

Type	Aliases	Size	Precision
`FLOAT`	`FLOAT4`, `REAL`	4 bytes	~7 decimal digits
`DOUBLE`	`FLOAT8`, `NUMERIC`	8 bytes	~15 decimal digits

-- Float precision warning
SELECT 0.1 + 0.2 = 0.3;  -- Returns FALSE due to floating point
SELECT 0.1::DOUBLE + 0.2::DOUBLE;  -- Still FALSE

Don't use FLOAT or DOUBLE for money. Use DECIMAL.

DECIMAL / NUMERIC#

DECIMAL(precision, scale)
-- precision: total number of digits
-- scale: digits after decimal point
 
-- Money: 10 total digits, 2 after decimal
amount DECIMAL(10, 2)  -- Stores up to 99,999,999.99
 
-- Percentage: 5 total digits, 4 after decimal  
rate DECIMAL(5, 4)  -- Stores up to 9.9999

DECIMAL performs exact arithmetic — no floating point errors.

String Types#

Type	Description
`VARCHAR`	Variable-length string, no limit
`VARCHAR(n)`	Variable-length, max n characters
`CHAR(n)`	Fixed-length, n characters (padded with spaces)
`TEXT`	Alias for VARCHAR
`BLOB`	Binary data

-- All of these are essentially equivalent in DuckDB
name VARCHAR,
name VARCHAR(255),
name TEXT,
name STRING

DuckDB doesn't enforce VARCHAR(n) length limits (unlike PostgreSQL). VARCHAR, VARCHAR(255), and TEXT all behave identically. Use VARCHAR for simplicity.

Boolean#

is_active BOOLEAN
 
-- Values
true, false, NULL
 
-- Comparisons
SELECT * FROM users WHERE is_active = true;
SELECT * FROM users WHERE is_active;  -- Same thing
SELECT * FROM users WHERE NOT is_active;

Date and Time Types#

Type	Description	Example
`DATE`	Calendar date	`2026-03-26`
`TIME`	Time of day	`14:30:00`
`TIMESTAMP`	Date + time (no timezone)	`2026-03-26 14:30:00`
`TIMESTAMPTZ`	Date + time (with timezone)	`2026-03-26 14:30:00+00`
`INTERVAL`	Duration	`INTERVAL '3 days'`

-- Date literals
SELECT DATE '2026-03-26';
SELECT '2026-03-26'::DATE;
 
-- Timestamp literals
SELECT TIMESTAMP '2026-03-26 14:30:00';
SELECT TIMESTAMPTZ '2026-03-26 14:30:00+00';
 
-- Current values
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, NOW();
 
-- Interval arithmetic
SELECT DATE '2026-03-26' + INTERVAL '7 days';
SELECT TIMESTAMP '2026-03-26 14:30:00' - INTERVAL '1 hour';
 
-- Date parts
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT EXTRACT(HOUR FROM NOW());
SELECT DATE_TRUNC('month', NOW());

Use TIMESTAMP for most datetime storage. Use TIMESTAMPTZ when you need timezone-aware operations.

JSON Type#

DuckDB treats JSON as text but with JSON-specific functions:

-- Store JSON
data JSON,
metadata VARCHAR  -- Also works for JSON
 
-- Query JSON
SELECT data->>'name' FROM records;  -- String extraction
SELECT data->'address'->>'city' FROM records;  -- Nested
 
-- JSON functions
SELECT json_extract(data, '$.name') FROM records;
SELECT json_extract_string(data, '$.name') FROM records;
SELECT json_array_length(data->'items') FROM records;
 
-- Create JSON
SELECT json_object('name', 'DenchClaw', 'version', 1);
SELECT json_array(1, 2, 3);

Arrays#

DuckDB has native array and list types:

-- Fixed-size array (DuckDB 0.10+)
tags INTEGER[3]
 
-- Variable-size list
tags INTEGER[]
emails VARCHAR[]
 
-- Array literals
SELECT [1, 2, 3]::INTEGER[];
SELECT ['hello', 'world']::VARCHAR[];
 
-- Array operations
SELECT array_length([1, 2, 3]);
SELECT list_contains([1, 2, 3], 2);  -- Returns true
SELECT list_append([1, 2, 3], 4);
SELECT unnest([1, 2, 3]);  -- Expands to 3 rows
 
-- Query with arrays
SELECT * FROM users WHERE list_contains(tags, 'enterprise');

Structs#

Structs are named collections of fields — like a row within a row:

-- Struct type
address STRUCT(street VARCHAR, city VARCHAR, zip VARCHAR)
 
-- Create structs
SELECT {'street': '123 Main St', 'city': 'San Francisco', 'zip': '94105'}::STRUCT(street VARCHAR, city VARCHAR, zip VARCHAR);
 
-- Access struct fields
SELECT address.city FROM contacts;
 
-- Unnest struct
SELECT unnest(address) FROM contacts;

MAP Type#

Maps are key-value collections with dynamic keys:

metadata MAP(VARCHAR, VARCHAR)
 
-- Create a map
SELECT MAP(['key1', 'key2'], ['val1', 'val2']);
SELECT MAP {'color': 'blue', 'size': 'large'};
 
-- Access map values
SELECT metadata['color'] FROM products;
 
-- Check if key exists
SELECT map_contains(metadata, 'color') FROM products;

ENUM Type#

Enums enforce valid values and are more efficient than VARCHAR for low-cardinality fields:

-- Create an enum type
CREATE TYPE deal_stage AS ENUM ('Lead', 'Qualified', 'Proposal', 'Negotiation', 'Closed Won', 'Closed Lost');
 
-- Use in table
CREATE TABLE deals (
    id VARCHAR,
    stage deal_stage
);
 
-- Insert
INSERT INTO deals VALUES ('d-1', 'Proposal');
 
-- Invalid value raises error
INSERT INTO deals VALUES ('d-2', 'Invalid Stage');  -- Error
 
-- List enum values
SELECT unnest(enum_range(NULL::deal_stage));

Enums use integer storage internally, so they're more efficient than VARCHAR for repeated values.

Type Casting#

-- Explicit cast
SELECT CAST(42 AS VARCHAR);
SELECT 42::VARCHAR;
SELECT '42'::INTEGER;
 
-- Safe cast (returns NULL on failure instead of error)
SELECT TRY_CAST('not a number' AS INTEGER);  -- Returns NULL
SELECT TRY_CAST('42' AS INTEGER);  -- Returns 42
 
-- Implicit casts
SELECT 1 + 1.5;  -- INTEGER + DOUBLE → DOUBLE
SELECT '2026-03-26'::DATE + 7;  -- DATE + INTEGER → DATE

NULL Handling#

-- NULL comparisons
SELECT NULL = NULL;   -- Returns NULL (not TRUE)
SELECT NULL IS NULL;  -- Returns TRUE
SELECT NULL IS NOT NULL;  -- Returns FALSE
 
-- NULL-safe operations
SELECT COALESCE(value, 'default');  -- First non-NULL
SELECT IFNULL(value, 'default');    -- Same as COALESCE with 2 args
SELECT NULLIF(value, '');           -- Return NULL if value = ''
 
-- NULL in aggregates
SELECT AVG(revenue) FROM orders;  -- Ignores NULL rows
SELECT COUNT(*) FROM orders;      -- Counts all rows including NULLs
SELECT COUNT(revenue) FROM orders;  -- Ignores NULL revenue rows

Data Types in DenchClaw#

DenchClaw maps its CRM field types to DuckDB types in the EAV schema:

DenchClaw Field Type	DuckDB Storage Type
`text`	`VARCHAR`
`number`	`DECIMAL(18, 4)`
`date`	`DATE`
`datetime`	`TIMESTAMP`
`email`	`VARCHAR`
`phone`	`VARCHAR`
`url`	`VARCHAR`
`enum`	`VARCHAR` (with valid values in field metadata)
`tags`	`VARCHAR[]` or stored as JSON
`boolean`	`BOOLEAN`
`richtext`	`TEXT` (Markdown)
`relation`	`VARCHAR` (foreign key UUID)

The entry_fields table stores all values as VARCHAR for schema flexibility. The PIVOT views cast them to the appropriate type when building the v_* views.