Yandex Cloud
  • Services
  • Solutions
  • Why Yandex Cloud
  • Pricing
  • Documentation
  • Contact us
Get started
Language / Region
© 2022 Yandex.Cloud LLC
  • Contents
  • Getting started
    • Overview
    • Creating a database
    • Authentication
    • YDB command line interface (CLI)
    • YQL query language
    • YDB SDK
    • Self-deployment
      • Overview
      • Docker
      • Binary file
      • Minikube
    • Useful links
    • Amazon DynamoDB-compatible Document API
      • Setting up AWS tools
      • Working with data from the HTTP interface
      • Working with the AWS CLI
        • Overview
        • Creating a table
        • Adding data to a table
        • Reading data from a table
        • Updating data
        • Data selections
        • Deleting created resources
      • Working with the AWS SDK
        • Overview
        • Creating a table
        • Uploading data to a table
        • Managing records in a table
          • Creating a record
          • Reading a record
          • Updating a record
          • Deleting a record
        • Searching and extracting data
        • Deleting a table
  • Practical guidelines
    • Deploying a web application
    • Developing a Slack bot
    • Connecting to YDB from a Python function in Yandex Cloud Functions
    • Connecting to a YDB database from a Yandex Cloud Functions function in Node.js
    • Converting a video to a GIF in Python
    • Developing a skill for Alice and a website with authorization
  • Concepts
    • Overview
    • Terms and definitions
    • Connecting to and authenticating with a database
    • Data model and schema
    • Serverless and Dedicated operation modes
    • Transactions
    • Secondary indexes
    • Time to Live (TTL)
    • Scan queries
    • Database limits
    • YDB cluster
      • Overview
      • General YDB schema
      • Disk subsystem of a cluster
    • Quotas and limits
  • Step-by-step instructions
    • Overview
    • Renaming
    • Using a query plan and AST
    • Reading and writing data
    • Working with secondary indexes
  • Recommendations
    • Overview
    • Schema design
    • Partitioning tables
    • Secondary indexes
    • Paginated output
    • Loading large data volumes
    • Using timeouts
  • Managing databases
    • Overview
    • Cloud management console
      • Overview
      • Creating, updating, and deleting databases
      • Tables and directories
      • Access management
    • Yandex.Cloud CLI
    • Backup and recovery
    • Diagnostics
      • Overview
      • System views
      • Monitoring
    • Metric reference
  • Pricing policy
    • Overview
    • Serverless mode
      • Pricing policy for serverless mode
      • Query cost for YQL
      • Request cost for the Document API
      • Request cost for special APIs
    • Dedicated mode
  • Amazon DynamoDB-compatible HTTP API
    • All methods
    • Actions
      • BatchGetItem
      • BatchWriteItem
      • CreateTable
      • DeleteItem
      • DeleteTable
      • DescribeTable
      • DescribeTimeToLive
      • GetItem
      • ListTables
      • PutItem
      • Query
      • Scan
      • TransactGetItems
      • TransactWriteItems
      • UpdateItem
      • UpdateTimeToLive
    • Common errors
  • YQL
    • Overview
    • Data types
      • Overview
      • Simple
      • Optional
      • Containers
      • Special
      • Type casting
      • Text representation of data types
      • JSON
    • Syntax
      • Overview
      • Lexical structure
      • Expressions
      • ACTION
      • ALTER TABLE
      • CREATE TABLE
      • DECLARE
      • DELETE
      • DISCARD
      • DROP TABLE
      • GROUP BY
      • FLATTEN
      • INSERT
      • INTO RESULT
      • JOIN
      • PRAGMA
      • REPLACE
      • SELECT
      • UPDATE
      • UPSERT
      • VALUES
      • WINDOW
      • Unsupported statements
    • Built-in functions
      • Overview
      • Basic
      • Aggregate
      • Window
      • For lists
      • For dictionaries
      • For structures
      • For types
      • For JSON
      • C++ libraries
        • Overview
        • Hyperscan
        • Pcre
        • Pire
        • Re2
        • String
        • Unicode
        • DateTime
        • Url
        • Ip
        • Yson
        • Digest
        • Math
        • Histogram
    • YQL tutorial
      • Overview
      • Creating a table
      • Adding data to a table
      • Selecting data from all columns
      • Selecting data from specific columns
      • Sorting and filtering
      • Data aggregation
      • Additional selection criteria
      • Joining tables with JOIN
      • Inserting and updating data with REPLACE
      • Inserting and updating data with UPSERT
      • Inserting data with INSERT
      • Updating data with UPDATE
      • Deleting data
      • Adding and deleting columns
      • Deleting a table
  • Working with the YDB CLI
    • Overview
    • Install
    • Structure of YDB CLI commands
    • Service commands
    • Connecting to and authenticating with a database
    • Global parameters
    • Working with the DB schema
      • List of objects
      • Information about the object
      • Directories
      • Secondary indexes
      • Renaming tables
    • Operations with data
      • Making a DB query
      • Query execution plan
      • Streaming table reads
      • Scan queries
    • Importing and exporting data
      • Overview
      • File structure of data export
      • Exporting data to the file system
      • Importing data from the file system
      • Connecting to and authenticating with S3
      • Exporting data to S3
      • Importing data from S3
    • Managing profiles
      • Overview
      • Creating a profile
      • Using a profile in requests
      • Getting profile information
      • Deleting a profile
      • Activated profile
    • Information services
      • List of endpoints
      • Authentication
    • Load testing
      • Overview
      • Stock load
  • Working with the YDB SDK
    • Overview
    • Install
    • Authentication
    • Test app
      • Overview
      • C++
      • C# (.NET)
      • Go
      • Java
      • Node.js
      • PHP
      • Python
      • Archive
        • Go v1
        • Go v2
    • Handling errors in the API
    • Code recipes
      • Overview
      • Authentication
        • Overview
        • Using a token
        • Anonymous
        • Service account file
        • Metadata service
        • Using environment variables
        • Username and password based
      • Balancing
        • Overview
        • Random choice
        • Prefer the nearest data center
        • Prefer the availability zone
      • Running repeat queries
      • Troubleshooting
        • Overview
        • Enable logging
        • Enable metrics in Prometheus
        • Enable tracing in Jaeger
      • Setting the session pool size
  • Managing a cluster
    • Overview
    • Kubernetes
      • Overview
      • Deploying in Yandex Managed Service for Kubernetes
      • Deploying in AWS Elastic Kubernetes Service
      • Use
    • Manual
      • Overview
      • Local deployment
      • Cluster configuration
      • Production checklist
      • Maintaining a cluster's disk subsystem
        • Overview
        • How to stay within the failure model
        • Disk load balancing
        • Methods to free up space on physical devices
        • Cluster extension
        • Adding storage groups
        • Safe restart and shutdown of nodes
        • Enabling/disabling SelfHeal
        • Enabling/disabling Scrubbing
        • Moving VDisks
        • Updating configurations via CMS
        • Updating configuration of the actor system
    • Embedded UI
      • Overview
      • YDB Monitoring
      • Hive web-viewer
      • Connections overview
      • Logs
      • Charts
    • System views
  • Questions and answers
    • Overview
    • General questions
    • Errors
    • YQL
    • Serverless
    • All questions on one page
  1. YQL
  2. Data types
  3. Simple

Primitive data types

Written by
Yandex Cloud
  • Numeric types
  • String types
  • Date and time
    • Supporting types with a time zone label
  • Explicit casting
    • Casting to numeric types
    • Converting to date and time data types
    • Conversion to other data types
  • Implicit casting
    • Numeric types
    • Date and time 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.
Not supported for table columns
Int16 A signed integer.
Acceptable values: from –215 to 215–1.
Not supported for table columns
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.
Not supported for table columns
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, precision is fixed: Decimal (22,9).
Can't be used in the primary key

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:

  • 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 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 — — —

Was the article helpful?

Language / Region
© 2022 Yandex.Cloud LLC
In this article:
  • Numeric types
  • String types
  • Date and time
  • Supporting types with a time zone label
  • Explicit casting
  • Casting to numeric types
  • Converting to date and time data types
  • Conversion to other data types
  • Implicit casting
  • Numeric types
  • Date and time types