Data Types

Data Types

db9 supports 24 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	Exact decimal number with at most 28–29 significant digits and 28 decimal places, even when a larger precision is declared. A larger value fails with 22P02 (invalid input syntax), arithmetic that exceeds it with 22003 (numeric field overflow), and decimal places beyond that capacity are rounded off without an error. 'NaN' and 'Infinity' are rejected (22P02). A declared precision and scale are enforced as in PostgreSQL: INSERT, UPDATE, COPY, a cast and a PL/pgSQL assignment round a value to the declared scale (12.345 becomes 12.35 in NUMERIC(5,2)) and fail with 22003 when it does not fit the declared precision — NUMERIC(5,2) rejects 1234.5. Two paths skip the check where PostgreSQL applies it: a cast to a domain over NUMERIC(p,s) (1234.5::my_domain returns 1234.50, although inserting into a column of the domain is checked), and a composite type’s NUMERIC(p,s) field, which a ROW(...) cast does not check and a text literal such as '(12.345,USD)' does not round. Over the HTTP SQL API values arrive as 64-bit floats — see SQL Execution
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 (microsecond precision)
TIMESTAMPTZ	TIMESTAMP WITH TIME ZONE	Timestamp with timezone (microsecond precision)
DATE	—	Calendar date (days)
TIME	TIME WITHOUT TIME ZONE	Time of day (microsecond precision). TIMETZ / TIME WITH TIME ZONE has basic support — values with a numeric UTC offset store, compare and sort, but casts, interval arithmetic, AT TIME ZONE and EXTRACT do not work on it; see the compatibility matrix
INTERVAL	—	Time interval (months + microseconds)
UUID	—	128-bit UUID
INET	—	IPv4/IPv6 address, host or network (192.168.1.1, 10.0.0.0/8, ::1). Equality and ordering supported; network operators (<<) and functions (host(), netmask()) not yet available, no CIDR type
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)

Accepted but widened: SMALLINT / INT2, REAL / FLOAT4 and CHAR(n) (fixed-length) have no distinct storage in DB9. A schema using them is accepted without error, but the column is created as INTEGER, DOUBLE PRECISION and VARCHAR(n) respectively — that is the type the catalog and your client will report back. Prefer the wider type explicitly.

CHAR(n) additionally loses PostgreSQL’s blank-padding semantics, which changes query results rather than just storage. PostgreSQL compares CHAR(n) as bpchar, whose equality ignores trailing blanks; DB9 compares it as varchar, whose equality does not. A predicate differing only in trailing spaces therefore matches on PostgreSQL and fails to match on DB9:

SQL

-- CHAR(5) column c, after INSERT 'ab'
SELECT octet_length(c);   -- PostgreSQL: 5 (padded)   DB9: 2 (not padded)
SELECT c = 'ab   ';       -- PostgreSQL: true         DB9: false
SELECT c = 'ab';          -- PostgreSQL: true         DB9: true

This cuts both ways after a migration: values that arrive already padded still compare equal to a padded literal, but stop matching the unpadded one on DB9. Trim on write, or compare with an explicit rtrim(), rather than relying on padding semantics. See Limits and Constraints.

Timestamp precision: Timestamps are stored at microsecond precision, matching standard PostgreSQL. A precision parameter rounds to the declared number of fractional digits, as in PostgreSQL: a TIMESTAMP(n), TIMESTAMPTZ(n) or TIME(n) column rounds the values stored in it — a value ending .123456 is rounded to .123 when it is stored in a TIMESTAMP(3) column — and a cast such as '...'::timestamp(3) or '...'::time(3) rounds the same way.

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 microseconds.

Interval Operators

Interval arithmetic works as in PostgreSQL. Unary negation applies to a literal, a column, or any interval expression:

SQL

SELECT -INTERVAL '1 day'                        AS negated_literal,
       -(INTERVAL '1 day' + INTERVAL '1 hour')  AS negated_expression;

▶ Run

An interval literal may appear on either side of a binary operator, intervals can be added to and subtracted from one another, and division is supported:

SQL

SELECT INTERVAL '1 day' * 2                  AS literal_on_left,
       2 * INTERVAL '1 day'                  AS literal_on_right,
       INTERVAL '1 day' / 2                  AS divided,
       INTERVAL '1 day' + INTERVAL '1 hour'  AS summed,
       INTERVAL '3 days' - INTERVAL '1 day'  AS subtracted,
       NOW() - 7 * INTERVAL '1 day'          AS a_week_ago;

▶ Run

Intervals held in a column or returned by an expression behave the same way:

SQL

SELECT i * 2 AS scaled, i / 2 AS halved, i + i AS summed, i - i AS zero, -i AS negated,
       NOW() + i AS tomorrow
FROM (SELECT 1 * INTERVAL '1 day' AS i) t;

▶ Run

Type Coercion

The type system uses the following coercion strategies; mixed text branches differ from PostgreSQL:

CASE / COALESCE / VALUES (“Text wins”)

When a TEXT value is mixed with a value of another type in CASE, COALESCE or a VALUES list, the result is TEXT: SELECT COALESCE(NULL::int, 'a'::text) returns a, and VALUES (1), ('a'::text) yields a text column. PostgreSQL rejects these two examples with 42804 because their integer and text types cannot be matched.

Two cases fall outside that rule:

• An untyped string literal is not TEXT yet — it takes the type of the other branch. CASE WHEN false THEN 1 ELSE 'a' END and SELECT 1 UNION SELECT 'a' both try to read 'a' as an integer and fail with 22P02 (invalid input syntax for type integer: "a"). Cast the literal ('a'::text) when you mean text.
• UNION does not unify TEXT with another type: SELECT 1 UNION SELECT 'a'::text fails with 42804 (UNION types INTEGER and TEXT cannot be matched). Cast the other branch instead — SELECT 1::text UNION SELECT 'a' returns two text rows.

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. Rounds fractional Float→Int as an explicit cast does, rejects Bool↔Int, errors on VARCHAR overflow
Implicit	Comparison coercion	Strictest. Rejects Bool↔Int, and never rounds: 3 = 2.7 is false

Cast	Explicit	Assignment
2.7::INTEGER	Rounds to 3	Rounds to 3
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