Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 38 ยท SQL Dialect and Types

Data Types and Casting in DuckDB

Learn DuckDB's rich type system: integers, DECIMAL, floating point, VARCHAR, BOOLEAN, dates, ENUM and UUID, plus CAST, the :: operator, TRY_CAST and division rules.

Types are where DuckDB differs most from SQLite. SQLite is lenient: any column can hold anything. DuckDB is strictly typed, like PostgreSQL. A column is an INTEGER or a DATE and the engine enforces it. That strictness catches mistakes early, makes storage compact and lets the engine pick fast code for each type. It also means you should know the types you have.

DuckDB's type behavior cannot be shown by the SQLite stand-in, which is dynamically typed. So this lesson uses read-only DuckDB blocks for type rules, and the runnable stand-ins show the portable parts (CAST, rounding, typeof) with the differences pointed out.

Numbers

Type Size Range / notes
TINYINT 1 byte -128 to 127
SMALLINT 2 bytes -32,768 to 32,767
INTEGER (INT) 4 bytes about +/- 2.1 billion
BIGINT 8 bytes about +/- 9.2e18
HUGEINT 16 bytes about +/- 1.7e38
UTINYINT, USMALLINT, UINTEGER, UBIGINT unsigned 0 up to the maximum
FLOAT (REAL) 4 bytes approximate, about 7 digits
DOUBLE 8 bytes approximate, about 15 digits
DECIMAL(w, s) up to 38 digits exact, w total digits, s after the point

Pick the smallest integer type that safely fits: a TINYINT column uses a quarter of the space of INTEGER before compression. Arithmetic that overflows a type raises an error rather than wrapping silently.

Exact versus approximate

FLOAT and DOUBLE store binary approximations, so 0.1 + 0.2 is not exactly 0.3. Money must use DECIMAL:

SQL
-- DuckDB (read-only example)
SELECT 0.1::DOUBLE + 0.2::DOUBLE = 0.3::DOUBLE AS doubles_equal;      -- false
SELECT 0.1::DECIMAL(10,2) + 0.2::DECIMAL(10,2) = 0.3::DECIMAL(10,2);   -- true
CREATE TABLE prices (item VARCHAR, amount DECIMAL(10, 2));

A bare literal such as 3.14 is a DECIMAL in DuckDB, while in SQLite it would be a float. A bare 42 is an INTEGER; one too large for it becomes BIGINT or HUGEINT automatically.

Division: a notable difference

In DuckDB, / always performs true division and returns a DOUBLE; use // for integer division:

SQL
-- DuckDB (read-only example)
SELECT 7 / 2;     -- 3.5
SELECT 7 // 2;    -- 3
SELECT 7 % 2;     -- 1

SQLite follows the older rule that dividing two integers truncates. Watch this in the stand-in:

SQL Stand-in: integer division in SQLite

Press Run (or Ctrl + Enter) to see the result here.

SQLite prints 3 for the first column. DuckDB would give 3.5. If you port SQL between engines, check every division.

Text, binary and booleans

  • VARCHAR (aliases TEXT, STRING) holds text of any length. DuckDB ignores a length like VARCHAR(20) in almost all cases; it is documentation only.
  • BLOB holds raw bytes.
  • BOOLEAN is true, false or NULL. Comparisons yield a real boolean.
  • UUID stores 128-bit identifiers compactly; gen_random_uuid() makes one.
  • ENUM is a fixed list of strings stored as small integers:
SQL
-- DuckDB (read-only example)
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE days (d DATE, feeling mood);
INSERT INTO days VALUES ('2026-01-01', 'happy');
INSERT INTO days VALUES ('2026-01-02', 'furious');   -- error: not a valid mood

An ENUM rejects bad values and compresses well; it is a good choice for columns like status or country code.

Dates and times (preview)

DATE, TIME, TIMESTAMP, TIMESTAMPTZ (with time zone) and INTERVAL have their own lesson: Dates, times and intervals.

Nested types (preview)

LIST, ARRAY, STRUCT, MAP and UNION can hold structured data inside one column, see Lists, structs and maps.

Casting

To convert a value to another type, use CAST(value AS type) or DuckDB's shorter value::type:

SQL
-- DuckDB (read-only example)
SELECT CAST('42' AS INTEGER);
SELECT '2026-03-15'::DATE;
SELECT 3.99::INTEGER;          -- 4 (rounds, does not truncate)
SELECT 42::VARCHAR || ' ducks';

If the cast is impossible, CAST fails and the whole query stops: SELECT 'abc'::INTEGER is a Conversion Error. For messy input, use TRY_CAST, which returns NULL instead of failing:

SQL
-- DuckDB (read-only example)
SELECT TRY_CAST('abc' AS INTEGER);     -- NULL
SELECT TRY_CAST('2026-13-45' AS DATE); -- NULL

The portable stand-in uses CAST, which in SQLite is forgiving (it gives 0 for text that is not a number):

SQL Stand-in: CAST in SQLite is forgiving

Press Run (or Ctrl + Enter) to see the result here.

SQLite returns 42, 42, 0 and 3. DuckDB would raise an error for the second and third, and round 3.99 to 4. The strict behavior protects you from silent garbage. A good pattern for dirty data is TRY_CAST plus a check for rows where the result is NULL but the input was not, to find the bad values:

SQL Stand-in: find values that are not numbers ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Implicit conversions

DuckDB converts types implicitly only when it is safe: an INTEGER can combine with a BIGINT or a DOUBLE, and a string literal can become a date when compared with a date column. It will not turn 'abc' into a number on its own. If you need a conversion, write it, as :: makes your intent visible.

NULL

Every type can be NULL, meaning "unknown". NULL = NULL is NULL, not true; test with IS NULL, and use IS NOT DISTINCT FROM for a null-safe comparison. Aggregates ignore NULLs, except count(*).

Inspecting types

SQL
-- DuckDB (read-only example)
SELECT typeof(1), typeof(1.5), typeof('a'), typeof(DATE '2026-01-01'), typeof([1,2]);
DESCRIBE SELECT * FROM 'data.csv';

typeof() tells you the type of an expression, and DESCRIBE shows every column's type for a table or a query, which is the first thing to check when reading a file.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does 7 / 2 return in DuckDB?

    In DuckDB, / is true division and returns a double. Use // for integer division.
  2. 2. Which type should you use for money amounts?

    DECIMAL stores exact values. Floating-point types hold binary approximations.
  3. 3. What does TRY_CAST('abc' AS INTEGER) return?

    TRY_CAST returns NULL when the conversion fails, instead of stopping the query.
  4. 4. What does the :: operator do?

    expr::TYPE is DuckDB's short form of CAST(expr AS TYPE).

Summary

  • DuckDB is strictly typed: pick TINYINT to HUGEINT, DECIMAL for exact numbers, DOUBLE for approximations.
  • / divides truly, // divides integers; SQLite's integer division differs.
  • CAST(x AS T) and x::T convert; TRY_CAST returns NULL on failure.
  • ENUM and UUID give constrained or compact columns; VARCHAR length is advisory.
  • typeof() and DESCRIBE reveal types.

Next: Lists, structs and maps