Primitive data types

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

Numeric types

Type	Description	Notes
`Bool`	Boolean value
`Int8`	Signed integer	Acceptable values: from –2⁷ to 2⁷–1
`Int16`	Signed integer	Acceptable values: from –2¹⁵ to 2¹⁵–1
`Int32`	Signed integer	Acceptable values: from –2³¹ to 2³¹–1
`Int64`	Signed integer	Acceptable values: from –2⁶³ to 2⁶³–1
`Uint8`	Unsigned integer	Acceptable values: from 0 to 2⁸–1
`Uint16`	Unsigned integer	Acceptable values: from 0 to 2¹⁶–1
`Uint32`	Unsigned integer	Acceptable values: from 0 to 2³²–1
`Uint64`	Unsigned integer	Acceptable values: from 0 to 2⁶⁴–1
`Float`	Real number with variable precision, 4 bytes in size	Can't be used in the primary key or in columns that form the key of a secondary index
`Double`	Real number with variable precision, 8 bytes in size	Can't be used in the primary key or in columns that form the key of a secondary index
`Decimal(precision, scale)`	Real number with fixed precision, 16 bytes in size. Precision is the maximum total number of decimal digits stored, takes values from 1 to 35. Scale is the maximum number of decimal digits stored to the right of the decimal point, takes values from 0 to the precision value.
`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×10¹²⁶–1, negative numbers from -1×10¹²⁶–1 to -1×10^-130, and 0. Compatible with the `Number` type in AWS DynamoDB. It's not recommended for ydb-native applications.	—

Examples

SELECT
  Bool("true"),
  Uint8("0"),
  Int32("-1"),
  Uint32("2"),
  Int64("-3"),
  Uint64("4"),
  Float("-5"),
  Double("6"),
  Decimal("1.23", 5, 2), -- up to 5 decimal digits, with 2 after the decimal point
  String("foo"),
  Utf8("Hello"),
  Yson("<a=1>[3;%false]"),
  Json(@@{"a":1,"b":null}@@),
  Date("2017-11-27"),
  Datetime("2017-11-27T13:24:00Z"),
  Timestamp("2017-11-27T13:24:00.123456Z"),
  Interval("P1DT2H3M4.567890S"),
  TzDate("2017-11-27,Europe/Moscow"),
  TzDatetime("2017-11-27T13:24:00,America/Los_Angeles"),
  TzTimestamp("2017-11-27T13:24:00.123456,GMT"),
  Uuid("f9d5cc3f-f1dc-4d9c-b97e-766e57ca4ccb");

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 or in columns that form the key of a secondary index
`JsonDocument`	JSON in an indexed binary representation	Doesn't support matching, can't be used in the primary key or in columns that form the key of a secondary index
`Yson`	YSON in a textual or binary representation.	Doesn't support matching, can't be used in the primary key or in columns that form the key of a secondary index
`Uuid`	Universally unique identifier UUID	—

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	Possible values	Size (bytes)	Notes
`Date`	A moment in time corresponding to midnight¹ in UTC, precision to the day	from 00:00 01.01.1970 to 00:00 01.01.2106	2	—
`Date32`	A moment in time corresponding to midnight¹ in UTC, precision to the day	from 00:00 01.01.144169 BC to 00:00 01.01.148107 AD	4	—
`Datetime`	A moment in time in UTC, precision to the second	from 00:00 01.01.1970 to 00:00 01.01.2106	4	—
`Datetime64`	A moment in time in UTC, precision to the second	from 00:00 01.01.144169 BC to 00:00 01.01.148107 AD	8	—
`Timestamp`	A moment in time in UTC, precision to the microsecond	from 00:00 01.01.1970 to 00:00 01.01.2106	8	—
`Timestamp64`	A moment in time in UTC, precision to the microsecond	from 00:00 01.01.144169 BC to 00:00 01.01.148107 AD	8	—
`Interval`	Time interval, precision to the microsecond	from -136 years to +136 years	8	Not available for column-oriented tables
`Interval64`	Time interval, precision to the microsecond	from -292277 years to +292277 years	8	Not available for column-oriented tables
`TzDate`	A moment in time in UTC corresponding to midnight in the specified timezone	from 00:00 01.01.1970 to 00:00 01.01.2106		Not supported in table columns
`TzDate32`	A moment in time in UTC corresponding to midnight in the specified timezone	from 00:00 01.01.144169 BC to 00:00 01.01.148107 AD	4 and timezone label	—
`TzDateTime`	A moment in time in UTC with timezone label and precision to the second	from 00:00 01.01.1970 to 00:00 01.01.2106		Not supported in table columns
`TzDateTime64`	A moment in time in UTC with timezone label and precision to the second	from 00:00 01.01.144169 BC to 00:00 01.01.148107 AD	8 and timezone label	—
`TzTimestamp`	A moment in time in UTC with timezone label and precision to the microsecond	from 00:00 01.01.1970 to 00:00 01.01.2106		Not supported in table columns
`TzTimestamp64`	A moment in time in UTC with timezone label and precision to the microsecond	from 00:00 01.01.144169 BC to 00:00 01.01.148107 AD	8 and timezone label	—

¹ Midnight refers to the time point where all time components equal zero.

Features of supporting types with timezone label

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

When converting (CAST, DateTime::Parse, DateTime::Format) to a string and from a string.
In DateTime::Split, a timezone component is added to Resource<TM>.

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	Int8	Int16	Int32	Int64	Uint8	Uint16	Uint32	Uint64	Float	Double	Decimal
Bool	—	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	Yes¹	No
Int8	Yes²	—	Yes	Yes	Yes	Yes³	Yes³	Yes³	Yes³	Yes	Yes	Yes
Int16	Yes²	Yes⁴	—	Yes	Yes	Yes^3,4	Yes³	Yes³	Yes³	Yes	Yes	Yes
Int32	Yes²	Yes⁴	Yes⁴	—	Yes	Yes^3,4	Yes^3,4	Yes³	Yes³	Yes	Yes	Yes
Int64	Yes²	Yes⁴	Yes⁴	Yes⁴	—	Yes^3,4	Yes^3,4	Yes^3,4	Yes³	Yes	Yes	Yes
Uint8	Yes²	Yes⁴	Yes	Yes	Yes	—	Yes	Yes	Yes	Yes	Yes	Yes
Uint16	Yes²	Yes⁴	Yes⁴	Yes	Yes	Yes⁴	—	Yes	Yes	Yes	Yes	Yes
Uint32	Yes²	Yes⁴	Yes⁴	Yes⁴	Yes	Yes⁴	Yes⁴	—	Yes	Yes	Yes	Yes
Uint64	Yes²	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	—	Yes	Yes	Yes
Float	Yes²	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes^3,4	Yes^3,4	Yes^3,4	Yes^3,4	—	Yes	No
Double	Yes²	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes^3,4	Yes^3,4	Yes^3,4	Yes^3,4	Yes	—	No
Decimal	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	—
String	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Utf8	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Json	No	No	No	No	No	No	No	No	No	No	No	No
Yson	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	Yes⁵	No
Uuid	No	No	No	No	No	No	No	No	No	No	No	No
Date	No	Yes⁴	Yes⁴	Yes	Yes	Yes⁴	Yes	Yes	Yes	Yes	Yes	No
Datetime	No	Yes⁴	Yes⁴	Yes⁴	Yes	Yes⁴	Yes⁴	Yes	Yes	Yes	Yes	No
Timestamp	No	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes⁴	Yes	Yes	Yes	No
Interval	No	Yes⁴	Yes⁴	Yes⁴	Yes	Yes^3,4	Yes^3,4	Yes^3,4	Yes³	Yes	Yes	No

¹ True is converted to 1 and False to 0.
² Any value other than 0 is converted to True, 0 is converted to False.
³ Possible only in case of a non-negative value.
⁴ Possible only within the valid range.
⁵ 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	Yes⁴	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

⁴ 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<Float>),   -- [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`	—	—	—

Was the article helpful?

Overview

Optional