Data Types

TiDB Cloud Lake stores data in strongly typed columns. This page summarizes the supported data types, how automatic/explicit conversions work, and what happens with NULL or default values.

Foundational Types

Date & Time Types

Structured & Semi-Structured Types

Domain-Specific Types

Casting and Conversion

Explicit Casting

• CAST(expr AS TYPE) uses ANSI syntax and fails when conversion is invalid.
• expr::TYPE is the PostgreSQL-style shorthand.
• TRY_CAST(expr AS TYPE) returns NULL instead of raising an error when conversion fails.

Implicit Casting (Coercion)

TiDB Cloud Lake performs automatic conversions in well-defined situations:

1. Integers upcast to INT64. Example: UInt8 -> INT64.
2. Numeric values upcast to FLOAT64 when necessary.
3. Any type T can become Nullable(T) if a NULL appears in an expression.
4. All types can upcast to VARIANT.
5. Complex types coerce element-wise (Array<T> -> Array<U> when T -> U; same for tuples/maps).

When a target column is NOT NULL, explicitly cast to Nullable<T> or use TRY_CAST if your data may contain NULLs.

SELECT CONCAT('1', col);      -- safe (strings)
SELECT CONCAT(1, col);        -- may fail if `col` can't coerce to number

NULL Handling and Defaults

Columns allow NULL values unless declared NOT NULL. When a NOT NULL column is omitted during INSERT, TiDB Cloud Lake writes a type-specific default value:

Example:

CREATE TABLE test (
    id   INT64,
    name STRING NOT NULL,
    age  INT32
);

INSERT INTO test (id, name, age) VALUES (2, 'Alice', NULL);  -- allowed
INSERT INTO test (id, name) VALUES (1, 'John');              -- age becomes NULL
INSERT INTO test (id, age) VALUES (3, 45);                   -- name uses default ''

Use DESC test or SHOW CREATE TABLE test to inspect column defaults and nullability at any time.