Data types

Data type is declared and enforced for each column in a table.

CQL data types

String types

Wrap strings in single quotes or double dollar signs ($$) in INSERT, UPDATE, and the SELECT statement WHERE clause.

When using single quotes, additional escaping is required for field values that contain single quotes or reserved characters, see Escaping characters.

For example, to insert a comment into a text field that contains a single quote and emoji:

INSERT INTO cycling.comments (
  id, created_at, comment
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  currentTimestamp(),
  $$ It's pouring rain, race should have been postponed :'( $$
);

Type

Constants supported

Description

ascii

strings

US-ASCII characters

text

strings

UTF-8 encoded string

varchar

strings

UTF-8 encoded string

Numeric types

Enter numeric types in plain text in the INSERT, UPDATE, and the SELECT statement WHERE clause.

For example, age = 31.

Integers

Type Constants supported Description

tinyint

integers

8-bit signed integer

smallint

integers

16-bit signed integer

int

integers

32-bit signed integer

bigint

integers

64-bit signed integer

varint

integers

Arbitrary-precision integer

Decimals

The default decimal separator is a period (.). Change the decimal separator in the driver settings or using cqlshrc decimalsep option. Internally, the decimal separator is stored as a period.

Type Constants supported Description

decimal

integers, floats

Variable-precision decimal Supports integers and floats.

When dealing with currency, it is a best practice to have a currency class that serializes to and from an int or use the decimal form.

float

integers, floats

32-bit IEEE-754 floating point

double

integers, floats

64-bit IEEE-754 floating point

Date and time types

INSERT or UPDATE date/time values using single quotes around string format or no quotes for integers. For example, setting a date in string format purchase_date = '2017-05-12' versus specifying it as an integer in days since epoch purchase_date = 17298.

Type Constants supported Description

date

strings

32-bit unsigned integer representing the number of days since epoch (January 1, 1970) with no corresponding time value

INSERT or UPDATE values as an integer (days since epoch) or in string format '<yyyy>-<mm>-<dd>', for example '2017-05-13'.

When loading data from CSV use the datetimeformat option in a cqlshrc file to change the cqlsh COPY TO date parsing format.

DateRangeType

strings

Stores a date range Truncated timestamps represent the entire date span or use the range syntax to create a custom range.

[<beginning_date> TO <end_date>]

Insert the custom ranges between square brackets. For example:

Example Description

2018-01

Beginning of the first day to the end of the last day in January 2018.

2018-01T15

Range includes hours of the day. 1500 to before 1600 (3pm to 4pm).

[2017-01-15 TO 2017-11-01]

The start of the fifteenth of January through the end of the first day of November.

[2017 TO 2017-11-01]

Start of 2017 until the end of the first day of November.

[* TO 2018-01-31]

From the earliest representable time through to the end of the day on 2018-01-31.

[2017-02-02T14:57:00 TO 2017-02-02T15:10:17]

From 14:57:00 to 15:10:17 on February 2, 2017.

If you specify a date instance using a date function, like currentDate(), you get the first millisecond of that day, not the entire day’s range.

The data type name is case sensitive. Use single quotes to specify this type in CQL statement.

duration

strings

Encoded as these 3 signed integers of variable lengths, where the integers represent the number of:

  • Months

  • Days

  • Nanoseconds

The number of months and days are decoded as 32-bit integers. The number of nanoseconds is decoded as a 64-bit integer. Provide the duration value using one of these formats:

  • Duration format: <N>y<N><N>mo<N>w<N>d<N><N>h<N>s<N>ms<N>us<N>ns. For example, 12h30m. The units are:

    • y - years (12 months)

    • mo - months (1 month)

    • w - weeks (7 days)

    • d - days (1 day)

    • h - hours (3,600,000,000,000 nanoseconds)

    • m - minutes (60,000,000,000 nanoseconds)

    • s - seconds (1,000,000,000 nanoseconds)

    • ms - milliseconds (1,000,000 nanoseconds)

    • us or µs - microseconds (1000 nanoseconds)

    • ns - nanoseconds (1 nanosecond)

  • ISO 8601 format: P[n]Y[n]M[n]DT[n]H[n]M[n]S or P[n]W

  • ISO 8601 alternative format: P[YYYY]-[MM]-[DD]T[hh]:[mm]:[ss]

Restriction: The PRIMARY KEY does not support duration type because it is not possible to determine if 1mo is greater than 29d without a date context.

time

strings

Encoded 64-bit signed integers representing the number of nanoseconds since midnight with no corresponding date value.

INSERT or UPDATE string format is '<hh>:<mm>:<ss>[.<fff>]', where milliseconds (<f>) are optional.

timestamp

strings

64-bit signed integer representing the date and time since epoch (January 1 1970 at 00:00:00 GMT) in milliseconds.

INSERT or UPDATE string format is ISO-8601; the string must contain the date and optionally can include the time and time zone, '<yyyy>-<mm>-<dd> [<hh>:<MM>:<ss>[.<fff>]][+/-<NNNN>]' where <NNNN> is the RFC 822 4-digit time zone specification (+0000 refers to GMT and US PST is -0800). If no time zone is specified, the client timezone is assumed. For example '2015-05-03 13:30:54.234-0800', '2015-05-03 13:30:54+0400', or '2015-05-03'.

Unique identifiers

Type Constants supported Description

uuid

uuids

128-bit universally unique identifier (UUID) Generate with the uuid function.

timeuuid

uuids

Version 1 UUID; unique identifier that includes a “conflict-free” timestamp. Generate with the now function.

Specialized types

Type Constants supported Description

blob

blobs

Arbitrary bytes (no validation), expressed as hexadecimal See Blob conversion functions.

boolean

booleans

True or false. Stored internally as true or false; when using the COPY TO in cqlsh to import or export data, change the format using the boolstyle option, for example when importing survey results that have yes/no style answer column.

counter

integers

64-bit signed integer Only one counter column is allowed per table. All other columns in a counter table must be PRIMARY KEYs. Increment and decrement the counter with an UPDATE statement using the + and - operators. Null values are not supported in the counter column, the initial count equals 0.

DseExecutorStateType

Reserved type used for Spark clients on analytic workload nodes.

[IMPORTANT] ==== The data type name is case sensitive. Use single quotes to specify this type in CQL statement. ====

inet

strings

IP address string in IPv4 or IPv6 format

static

any

A column that is shared by all rows in a partition with a single value. See Create a static column.

Geospatial types

Type Constants supported Description

PointType

Contains two coordinate values for latitude and longitude. See Creating a table with a geospatial type for details on entering point information.

The data type name is case sensitive. Use single quotes to specify this type in CQL statement.

LineStringType

Comma separate list of points See Creating a table with a geospatial type for details on entering linestring information.

The data type name is case sensitive. Use single quotes to specify this type in CQL statement.

PolygonType

Set of two linestrings

The data type name is case sensitive. Use single quotes to specify this type in CQL statement.

Collection types

Collection data types are a way to group and store data together in a column. Use collections to store small amounts of data, such as phone numbers, tags, or addresses. Collections are not appropriate for data that is expected to grow unbounded, such as all events for a particular user. For unbounded grouping of data, use a table with one or more clustering columns.

Collections can be indexed for more versatile querying.

Collections can be frozen or non-frozen. Frozen collections are stored as a single value and are not updatable. They must be read and written as a whole. Non-frozen collections store data that can be updated independently. Non-frozen collections are the default. In addition, some non-frozen list collection operations require a read-before-write, such as inserting an element at a specific index. Sets and maps never incur a read-before-write internally on insertion, thus, using a set over a list whenever possible is recommended.

Collections can be nested, provided the nested collection is frozen.

In a non-frozen collection, a tombstone is created for an insert and a non-incremental update in the collection. An incremental update adds a value to an existing value in the collection. The inserts and non-incremental updates for a non-frozen collection can cause large numbers of tombstones.

Use FROZEN on a set, map, or list to serialize multiple components into a single value, frozen<<collection_definition>>. Non-frozen types allow updates to individual fields, but values in a frozen collection are treated like blobs, any upsert overwrites the entire value.

Type Constants supported Description

set

n/a

Comma separated list of unique values sorted by position starting at zero. Supports appending and prepending elements in INSERT and UPDATE statements using the + and - operators if non-frozen.

list

n/a

Comma separated list of non-unique values of the same data type, list[data_type]. Elements are ordered by their position in the list; the first position is zero. Supports appending and prepending elements in INSERT and UPDATE statements using the + and - operators if non-frozen.

Lists have limitations and performance impact, whenever possible use a set or a frozen list, for example FROZEN<LIST<int>>. The append and prepend operations are not idempotent. If either of these operations timeout, the retry operation may (or may not) result in appending or prepending the value twice.

Also list operations are not idempotent by nature and can cause timeout issues in the case of retries.

map

n/a

Set of key-value pairs, where keys are unique and the map is sorted by its keys. Supports appending and prepending elements in INSERT and UPDATE statements using the + and - operators if non-frozen.

For INSERT and UPDATE, setting TTL is only apply to the newly inserted/updated elements.

Other types

Type Constants supported Description

tuple

n/a

Fixed length set of elements of different types. A tuple is always frozen without the need of the frozen keyword. The entire field is overwritten when using INSERT and UPDATE, therefore the expressions must provide a value for each element; explicitly declare null for elements that have no value. Because tuples are frozen by default, tuples can contain nested tuples and be nested in other data types For example, tuple<int,tuple<text,text>,boolean> and set<tuple<text,inet> are both valid data type declarations.

user defined type (UDT)

n/a

Customized data type that belongs to a specific keyspace. The UDT is only available in the keyspace where it is created. The system_schema.types contains a list of all UDTs, with the keyspace_name, type_name, field_names, and field_types.

Vector type

CQL supports storing multiple values in a single column in a vector array with a limited data type for the array values. Use vectors for storing AI embeddings, with values of float32.

Vectors are limited to a maximum dimension of 8K (2^13) items.

Deprecated types

The following types are supported for backward compatibility only.

Type Description

custom type (Deprecated supported for backward compatibility.)

Customized type added as a sub-class to AbstractType, where the class name is fully qualified or relative to the org.apache.cassandra.db.marshal package. Replaced by user defined type (UDT).

Java 11 type to CQL type mapping

The Java types, from which most CQL types are derived, are obvious to Java programmers. The derivation of the following types, however, might not be obvious:

CQL Type Java Type

decimal

java.math.BigDecimal

float

java.lang.Float

double

java.lang.Double

varint

java.math.BigInteger

CQL type compatibility

CQL data types have strict requirements for conversion compatibility. The following table shows the allowed alterations for data types:

Original data type: Data type to convert to:

ascii, bigint, boolean, decimal, double, float, inet, int, timestamp, timeuuid, uuid, varchar, varint

blob

int

varint

text

varchar

timeuuid

uuid

varchar

text

Clustering columns have even stricter requirements, because clustering columns mandate the order in which data is written to disk. The following table shows the allowed alterations for data types used in clustering columns:

Original data type: Data type to convert to:

int

varint

text

varchar

varchar

text

Related information:

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com