Numeric Data Types

Learning Focus

Use this lesson to understand Numeric Data Types in DuckDB and choose the right type for your data.

Integer Types

Type	Storage	Range
TINYINT	1 byte	-128 to 127
SMALLINT	2 bytes	-32,768 to 32,767
INTEGER	4 bytes	-2.1B to 2.1B
BIGINT	8 bytes	-9.2 quintillion to 9.2 quintillion
HUGEINT	16 bytes	Up to 1.8 × 10^38
UBIGINT	8 bytes	0 to 18.4 quintillion (unsigned)

CREATE TABLE counters (
    tiny_val  TINYINT,
    small_val SMALLINT,
    int_val   INTEGER,
    big_val   BIGINT,
    huge_val  HUGEINT
);

INSERT INTO counters VALUES (100, 30000, 1500000, 9000000000, 1234567890123456789);

SELECT * FROM counters;

Floating-Point Types

Type	Storage	Precision
FLOAT	4 bytes	~7 decimal digits
DOUBLE	8 bytes	~15 decimal digits

CREATE TABLE measurements (
    temperature FLOAT,
    latitude    DOUBLE,
    longitude   DOUBLE
);

INSERT INTO measurements VALUES (36.6, 37.7749295, -122.4194155);
SELECT * FROM measurements;

Float Precision

Floating-point types may lose precision in financial calculations. Use DECIMAL for money.

Decimal / Numeric Types

-- DECIMAL(precision, scale)
-- precision = total significant digits
-- scale = digits after decimal point

CREATE TABLE financials (
    revenue  DECIMAL(18,2),
    tax_rate DECIMAL(5,4)
);

INSERT INTO financials VALUES (1500000.75, 0.2100);
SELECT revenue * (1 + tax_rate) AS total FROM financials;

Special Numeric Types

-- Sequence-like behavior
CREATE SEQUENCE emp_seq START 1;
SELECT nextval('emp_seq');  -- 1
SELECT nextval('emp_seq');  -- 2

-- Bit operations
SELECT 5::INTEGER & 3;  -- 1 (bitwise AND)
SELECT 5::INTEGER | 3;  -- 7 (bitwise OR)

Type Casting

SELECT 42::BIGINT;
SELECT '3.14'::DOUBLE;
SELECT CAST(100 AS DECIMAL(10,2));
SELECT TRY_CAST('abc' AS INTEGER);  -- returns NULL instead of error

Common Pitfalls

Pitfall	Consequence	Prevention
Using FLOAT for currency	Rounding errors in sums	Use DECIMAL(18,2) for money
INTEGER overflow on large aggregations	Incorrect results	Use BIGINT or HUGEINT for COUNT on large tables
Implicit type coercion surprises	Unexpected query results	Use explicit CAST() or ::type syntax

Quick Reference

SELECT 42::INTEGER, 3.14::DOUBLE, 100.50::DECIMAL(10,2);
SELECT typeof(42);           -- integer
SELECT typeof(3.14);         -- double
SELECT typeof(100::DECIMAL); -- decimal(18,3)

What's Next

• Next: String Data Types - Learn text and binary types in DuckDB.
• Module Overview - Return to this module index.