Data types

The following describes the data types supported in YCQL, from the basic data types to collections, and user defined types.

The JSONB document data type is described in a separate section.

Strings

The following character types are supported:

varchar and text are aliases.

The following Apache Cassandra character types are not supported:

To test YugabyteDB support for character types, create a table that has columns with the following types specified:

CREATE KEYSPACE types_test;
USE types_test;

CREATE TABLE char_types (
  id int PRIMARY KEY,
  a TEXT,
  b VARCHAR
);

Insert the following rows into the table:

INSERT INTO char_types (id, a, b) VALUES (
  1, 'Data for the text column', 'Data for the varchar column'
);

Numeric types

The following numeric types are supported:

The following example creates a table with integer type columns and inserts rows into it:

CREATE TABLE albums (
  album_id BIGINT PRIMARY KEY,
  title VARCHAR,
  play_time SMALLINT,
  library_record INT
);

INSERT INTO albums (album_id, title, play_time, library_record)
values (3223372036854775808,'Funhouse', 3600,2146483645 );

Similarly, the following example shows how to create a table with floating-point typed columns and insert a row into that table:

CREATE TABLE floating_point_test (
  float_test FLOAT PRIMARY KEY,
  decimal_test DECIMAL
);

INSERT INTO floating_point_test (float_test, decimal_test)
VALUES (92233720368547.75807, 5.36152342);

Date and time

Temporal data types allow us to store date and time data. The following date and time types are supported in YugabyteDB:

The following example creates a table with the temporal types:

CREATE TABLE temporal_types (
  date_type DATE PRIMARY KEY,
  time_type TIME,
  timestamp_type TIMESTAMP
);

The following example inserts a row into the table:

INSERT INTO temporal_types (
  date_type, time_type, timestamp_type)
VALUES
  ('2000-06-28', '06:23:00', '2016-06-22 19:10:25');

The following shows the inserted data:

ycqlsh> select * from temporal_types;

 date_type  | time_type          | timestamp_type
------------+--------------------+---------------------------------
 2000-06-28 | 06:23:00.000000000 | 2016-06-23 00:10:25.000000+0000

(1 rows)

Universally unique ID types

A universally unique identifier (or UUID) is commonly used in distributed databases for generating unique identifiers without coordination from a central authority since that can become a bottleneck. These IDs are then used to identify unique rows in a database table. YugabyteDB supports two versions of UUIDs:

TIMEUUID is typically used when time-ordered unique identifiers are required in time-series use cases.

The following example creates a table with the UUID types:

CREATE TABLE iot (
  sensor_id UUID,
  measurement_id TIMEUUID,
  measurement FLOAT,
  PRIMARY KEY (sensor_id, measurement_id)
);

The following example inserts a row into the table:

INSERT INTO iot (
  sensor_id, measurement_id, measurement)
VALUES
  (28df63b7-cc57-43cb-9752-fae69d1653da, 4eb369b0-91de-11bd-8000-000000000000, 98.4);

The following shows the inserted data:

ycqlsh> select * from iot;

 sensor_id                            | measurement_id                       | measurement
--------------------------------------+--------------------------------------+-------------
 28df63b7-cc57-43cb-9752-fae69d1653da | 4eb369b0-91de-11bd-8000-000000000000 |        98.4

(1 rows)

Collection types

A collection data type allows storage of multi-valued columns. YugabyteDB supports the following types of collections:

The following example creates a table with the collection types:

CREATE TABLE user_profile (
  user_id UUID,
  user_name TEXT,
  recent_logins LIST<TIMESTAMP>,
  phone_numbers MAP<TEXT,TEXT>,
  account_numbers SET<TEXT>,
  PRIMARY KEY (user_id)
);

The following example inserts a row into the table:

INSERT INTO user_profile (
  user_id, user_name, recent_logins, phone_numbers, account_numbers)
VALUES
  (28df63b7-cc57-43cb-9752-fae69d1653da, 'John Doe', ['2023-02-03T04:05:00+0000'], {'home':'669-555-1212','work':'408-555-2121'},
  {'sa-1011212'});

The following shows the inserted data:

ycqlsh> select * from user_profile;

 user_id                              | user_name | recent_logins                       | phone_numbers                                    | account_numbers
--------------------------------------+-----------+-------------------------------------+--------------------------------------------------+-----------------
 28df63b7-cc57-43cb-9752-fae69d1653da |  John Doe | ['2023-02-03 04:05:00.000000+0000'] | {'home': '669-555-1212', 'work': '408-555-2121'} |  {'sa-1011212'}

(1 rows)

When the user logs in again, the recent_logins LIST column can be updated as shown below:

UPDATE user_profile
SET recent_logins = recent_logins + ['2023-04-05 09:15:08.000000+0000']
WHERE user_id = 28df63b7-cc57-43cb-9752-fae69d1653da;

ycqlsh> select * from user_profile;

 user_id                              | user_name | recent_logins                                                          | phone_numbers                                    | account_numbers
--------------------------------------+-----------+------------------------------------------------------------------------+--------------------------------------------------+-----------------
 28df63b7-cc57-43cb-9752-fae69d1653da |  John Doe | ['2011-02-03 04:05:00.000000+0000', '2023-04-05 09:15:08.000000+0000'] | {'home': '669-555-1212', 'work': '408-555-2121'} |  {'sa-1011212'}

(1 rows)

The preceding example appends the new element to an existing list. Prepending is also possible, as follows:

UPDATE user_profile
SET recent_logins = ['2023-04-05 09:15:08.000000+0000'] + recent_logins
WHERE user_id = 28df63b7-cc57-43cb-9752-fae69d1653da;

ycqlsh> select * from user_profile;

 user_id                              | user_name | recent_logins                                                          | phone_numbers                                    | account_numbers
--------------------------------------+-----------+------------------------------------------------------------------------+--------------------------------------------------+-----------------
 28df63b7-cc57-43cb-9752-fae69d1653da |  John Doe | ['2023-04-05 09:15:08.000000+0000', '2011-02-03 04:05:00.000000+0000'] | {'home': '669-555-1212', 'work': '408-555-2121'} |  {'sa-1011212'}

(1 rows)

SET and MAP work similarly, except that these types do not have a notion of prepending and the syntax for literals is slightly different. See YCQL Collections for more details.

User defined types

A user defined type is a collection of data types similar to a struct in a programming language.

The following example shows how to create and use a user defined type.

1. Create a user defined type.

   CREATE TYPE inventory_item (
      name text,
      supplier_id integer,
      price float
   );
   

2. Create a table with a user defined type as follows:

   CREATE TABLE on_hand (
      item_id UUID PRIMARY KEY,
      item inventory_item,
      count integer
   );
   

3. Insert a row as follows:

   INSERT INTO on_hand (item_id, item, count) VALUES (28df63b7-cc57-43cb-9752-fae69d1653da, {name: 'fuzzy dice', supplier_id: 42, price: 1.99}, 1000);
   

4. To select data from the on_hand example table, execute the following:

   SELECT * FROM on_hand WHERE item_id = 28df63b7-cc57-43cb-9752-fae69d1653da;
   

   Expect the following output:

    item_id                              | item                                               | count
   --------------------------------------+----------------------------------------------------+-------
    28df63b7-cc57-43cb-9752-fae69d1653da | {name: 'fuzzy dice', supplier_id: 42, price: 1.99} |  1000
   
   (1 rows)