DuckDB

Apply these conventions when working with DuckDB.

Data Types

Numeric: TINYINT (1B), SMALLINT (2B), INTEGER (4B), BIGINT (8B), HUGEINT (16B) + unsigned variants (UTINYINT, etc.). FLOAT (4B), DOUBLE (8B). DECIMAL(prec, scale) for exact math. Use smallest type that fits. String: VARCHAR (variable-length, aliases: TEXT, STRING). BLOB for binary. Date/Time: DATE, TIME, TIMESTAMP (aliases: DATETIME), TIMESTAMPTZ, INTERVAL. Precision variants: TIMESTAMP_S, TIMESTAMP_MS, TIMESTAMP_NS. Other: BOOLEAN, UUID, BIT, ENUM, JSON (requires json extension).

Complex types:

• LIST — variable-length, same-type: INTEGER[] or LIST(INTEGER). Literal: [1, 2, 3]. Different row lengths OK.
• ARRAY — fixed-length, same-type: INTEGER[3]. Every row must have same element count.
• STRUCT — named fields, different types: STRUCT(name VARCHAR, age INTEGER). Literal: {'name': 'Alice', 'age': 30}. All rows must have same keys.
• MAP — key-value, variable keys per row: MAP(VARCHAR, INTEGER). Literal: MAP {'a': 1, 'b': 2}. Bracket access returns LIST, not scalar.
• UNION — tagged union: UNION(num INTEGER, str VARCHAR). Create: union_value(num := 42). Query tag: union_tag(col).
• ENUM — ordered set of strings: CREATE TYPE mood AS ENUM ('happy', 'sad'). Stored as integers internally. Ordering follows definition order, not lexicographic — always cast literals when comparing: WHERE p >= 'medium'::priority.