Data Types

db9 supports 23 data types. All data is stored in UTF-8 encoding.

SQL Type	Aliases	Description
`BOOLEAN`	`BOOL`	True/false values
`INTEGER`	`INT`, `INT4`	4-byte signed integer
`BIGINT`	`INT8`	8-byte signed integer
`DOUBLE PRECISION`	`FLOAT8`	8-byte IEEE 754 floating point
`NUMERIC`	`NUMERIC(p,s)`, `DECIMAL`	Arbitrary-precision number (up to 28-29 significant digits)
`TEXT`	—	Variable-length UTF-8 string
`VARCHAR(n)`	`CHARACTER VARYING(n)`	Length-limited text (max n characters)
`BYTEA`	—	Raw byte array
`TIMESTAMP`	`TIMESTAMP WITHOUT TIME ZONE`	Timestamp (millisecond precision)
`TIMESTAMPTZ`	`TIMESTAMP WITH TIME ZONE`	Timestamp with timezone (millisecond precision)
`DATE`	—	Calendar date (days)
`TIME`	`TIME WITHOUT TIME ZONE`	Time of day (microsecond precision)
`INTERVAL`	—	Time interval (months + milliseconds)
`UUID`	—	128-bit UUID
`JSON`	—	JSON data (original text preserved)
`JSONB`	—	JSON data in canonical binary form
`SERIAL`	—	Auto-incrementing 4-byte integer
`BIGSERIAL`	—	Auto-incrementing 8-byte integer
`type[]`	—	Array of any supported type
`vector(N)`	—	Dense float vector of N dimensions (pgvector-compatible)
`TSVECTOR`	—	Full-text search document representation
`TSQUERY`	—	Full-text search query
`NAME`	—	63-byte identifier (PostgreSQL system type)

Not supported: SMALLINT / INT2, REAL / FLOAT4, CHAR(n) (fixed-length). Use INTEGER, DOUBLE PRECISION, and VARCHAR(n) instead.

Timestamp precision: Timestamps are stored at millisecond precision, not microsecond as in standard PostgreSQL. Precision parameters 0–6 are accepted in syntax, but sub-millisecond precision cannot be represented.

Interval Representation

Intervals store months and sub-month components separately, enabling calendar-aware arithmetic (e.g., adding 1 month to January 31 gives February 28/29). The sub-month component is stored in milliseconds.

Type Coercion

The type system uses two coercion strategies matching PostgreSQL behavior:

UNION / CASE / COALESCE / VALUES (“Text wins”)

When mixing Text with a typed value, the result is Text. Example: SELECT 1 UNION SELECT 'a' resolves to TEXT.

Comparisons (“Non-Text wins”)

When comparing Text with a typed value, the text literal is coerced to the typed side. Example: WHERE col = '42' coerces '42' to the column’s type.

Numeric Promotion Hierarchy

Int32 → Int64 → Float64 → Numeric. When two numeric types are mixed, the higher-precedence type wins.

Temporal Promotion

Date + Timestamp = Timestamp
Timestamp + TimestampTz = TimestampTz
Date + TimestampTz = TimestampTz

CAST Contexts

Context	When Used	Behavior
Explicit	`CAST(x AS type)` or `x::type`	Most permissive. Allows rounding (Float to Int), Bool↔Int, VARCHAR truncation
Assignment	INSERT/UPDATE column coercion	Medium. Rejects fractional Float→Int, rejects Bool↔Int, errors on VARCHAR overflow
Implicit	Comparison coercion	Strictest. Similar to Assignment

Cast	Explicit	Assignment
`2.7::INTEGER`	Rounds to 3	Rejects (fraction)
`TRUE::INTEGER`	Returns 1	Rejects
`42::BOOLEAN`	Returns true	Rejects
`'hello'::VARCHAR(3)`	Truncates to “hel”	Errors if > 3 chars
`NULL` to any type	Always succeeds	Always succeeds