Primitive data types

The terms "simple", "primitive", and "elementary" data types are used synonymously.

Numeric types

Type Description Notes
Bool Boolean value.
Int8 A signed integer.
Acceptable values: from -27 to 27–1.
Int16 A signed integer.
Acceptable values: from –215 to 215–1.
Int32 A signed integer.
Acceptable values: from –231 to 231–1.
Int64 A signed integer.
Acceptable values: from –263 to 263–1.
Uint8 An unsigned integer.
Acceptable values: from 0 to 28–1.
Uint16 An unsigned integer.
Acceptable values: from 0 to 216–1.
Uint32 An unsigned integer.
Acceptable values: from 0 to 232–1.
Uint64 An unsigned integer.
Acceptable values: from 0 to 264–1.
Float A real number with variable precision, 4 bytes in size. Can't be used in the primary key
Double A real number with variable precision, 8 bytes in size. Can't be used in the primary key
Decimal A real number with the specified precision, up to 35 decimal digits When used in table columns, the precision is fixed: Decimal(22,9)
DyNumber A binary representation of a real number with an accuracy of up to 38 digits.
Acceptable values: positive numbers from 1×10-130 up to 1×10126–1, negative numbers from -1×10126–1 to -1×10-130, and 0.
Compatible with the Number type in AWS DynamoDB. It's not recommended for ydb-native applications.

String types

Type Description Notes
String A string that can contain any binary data
Utf8 Text encoded in UTF-8
Json JSON represented as text Doesn't support matching, can't be used in the primary key
JsonDocument JSON in an indexed binary representation Doesn't support matching, can't be used in the primary key
Yson YSON in a textual or binary representation. Doesn't support matching, can't be used in the primary key
Uuid Universally unique identifier UUID Not supported for table columns

Cell size restrictions

The maximum value size for a non-key column cell with any string data type is 8 MB.

Unlike the JSON data type that stores the original text representation passed by the user, JsonDocument uses an indexed binary representation. An important difference from the point of view of semantics is that JsonDocument doesn't preserve formatting, the order of keys in objects, or their duplicates.

Thanks to the indexed view, JsonDocument lets you bypass the document model using JsonPath without the need to parse the full content. This helps efficiently perform operations from the JSON API, reducing delays and cost of user queries. Execution of JsonDocument queries can be up to several times more efficient depending on the type of load.

Due to the added redundancy, JsonDocument is less effective in storage. The additional storage overhead depends on the specific content, but is 20-30% of the original volume on average. Saving data in JsonDocument format requires additional conversion from the textual representation, which makes writing it less efficient. However, for most read-intensive scenarios that involve processing data from JSON, this data type is preferred and recommended.

Warning

To store numbers (JSON Number) in JsonDocument, as well as for arithmetic operations on them in the JSON API, the Double type is used. Precision might be lost when non-standard representations of numbers are used in the source JSON document.

Date and time

Type Description Notes
Date Date, precision to the day Range of values for all time types except Interval: From 00:00 01.01.1970 to 00:00 01.01.2106. Internal Date representation: Unsigned 16-bit integer
Datetime Date/time, precision to the second Internal representation: Unsigned 32-bit integer
Timestamp Date/time, precision to the microsecond Internal representation: Unsigned 64-bit integer
Interval Time interval (signed), precision to microseconds Value range: From -136 years to +136 years. Internal representation: Signed 64-bit integer. Can't be used in the primary key
TzDate Date with time zone label, precision to the day Not supported in table columns
TzDateTime Date/time with time zone label, precision to the second Not supported in table columns
TzTimestamp Date/time with time zone label, precision to the microsecond Not supported in table columns

Supporting types with a time zone label

Time zone label for the TzDate, TzDatetime, TzTimestamp types is an attribute that is used:

The point in time for these types is stored in UTC, and the timezone label doesn't participate in any other calculations in any way. For example:

select --these expressions are always true for any timezones:  the timezone doesn't affect the point in time.
    AddTimezone(CurrentUtcDate(), "Europe/Moscow") ==
        AddTimezone(CurrentUtcDate(), "America/New_York"),
    AddTimezone(CurrentUtcDatetime(), "Europe/Moscow") == 
        AddTimezone(CurrentUtcDatetime(), "America/New_York");

Keep in mind that when converting between TzDate and TzDatetime, or TzTimestamp the date's midnight doesn't follow the local time zone, but midnight in UTC for the date in UTC.

Casting between data types

Explicit casting

Explicit casting using CAST:

Casting to numeric types

Type Bool Int Uint Float Double Decimal
Bool — Yes1 Yes1 Yes1 Yes1 No
INT Yes2 — Yes3 Yes Yes Yes
Uint Yes2 Yes — Yes Yes Yes
Float Yes2 Yes Yes — Yes No
Double Yes2 Yes Yes Yes — No
Decimal No Yes Yes Yes Yes —
String Yes Yes Yes Yes Yes Yes
Utf8 Yes Yes Yes Yes Yes Yes
Json No No No No No No
Yson Yes4 Yes4 Yes4 Yes4 Yes4 Yes4
Uuid No No No No No No
Date No Yes Yes Yes Yes No
Datetime No Yes Yes Yes Yes No
Timestamp No Yes Yes Yes Yes No
Interval No Yes Yes Yes Yes No

1 True is converted to 1 and False to 0.
2 Any value other than 0 is converted to True, 0 is converted to False.
3 Possible only in the case of a non-negative value.
4 Using the built-in function Yson::ConvertTo.

Converting to date and time data types

Type Date Datetime Timestamp Interval
Bool No No No No
INT Yes Yes Yes Yes
Uint Yes Yes Yes Yes
Float No No No No
Double No No No No
Decimal No No No No
String Yes Yes Yes Yes
Utf8 Yes Yes Yes Yes
Json No No No No
Yson No No No No
Uuid No No No No
Date — Yes Yes No
Datetime Yes — Yes No
Timestamp Yes Yes — No
Interval No No No —

Conversion to other data types

Type String Utf8 Json Yson Uuid
Bool Yes No No No No
INT Yes No No No No
Uint Yes No No No No
Float Yes No No No No
Double Yes No No No No
Decimal Yes No No No No
String — Yes Yes Yes Yes
Utf8 Yes — No No No
Json Yes Yes — No No
Yson Yes4 No No No No
Uuid Yes Yes No No —
Date Yes Yes No No No
Datetime Yes Yes No No No
Timestamp Yes Yes No No No
Interval Yes Yes No No No

4 Using the built-in function Yson::ConvertTo.

Examples

SELECT
CAST("12345" AS Double), -- 12345.0
CAST(1.2345 AS Uint8), -- 1
CAST(12345 AS String), -- "12345"
CAST("1.2345" AS Decimal(5, 2)), -- 1.23
CAST("xyz" AS Uint64) IS NULL, -- true, because it failed
CAST(-1 AS Uint16) IS NULL, -- true, a negative integer cast to an unsigned integer
CAST([-1, 0, 1] AS List<Uint8?>), -- [null, 0, 1]
--The item type is optional: the failed item is cast to null.
CAST(["3.14", "bad", "42"] AS List), -- [3.14, 42]
--The item type is not optional: the failed item has been deleted.
CAST(255 AS Uint8), -- 255
CAST(256 AS Uint8) IS NULL -- true, out of range

Implicit casting

Implicit type casting that occurs in basic operations ( +-*/) between different data types. The table cells specify the operation result type, if the operation is possible:

Numeric types

Type Int Uint Float Double
INT — INT Float Double
Uint INT — Float Double
Float Float Float — Double
Double Double Double Double —

Date and time types

Type Date Datetime Timestamp Interval TzDate TzDatetime TzTimestamp
Date — — — Date — — —
Datetime — — — Datetime — — —
Timestamp — — — Timestamp — — —
Interval Date Datetime Timestamp — TzDate TzDatetime TzTimestamp
TzDate — — — TzDate — — —
TzDatetime — — — TzDatetime — — —
TzTimestamp — — — TzTimestamp — — —
Previous
Overview
Next
Optional
In this article: