Create a table column

Columns are defined at the time of table creation or during table modification.

To create or alter a table, a keyspace must exist.

All data types found in CQL can be used when creating a table column.

String and numeric types

There are several string and numeric types available in CQL. It is important that you choose an appropriate type for the data you are storing. Don’t use string types to store numeric values, and don’t use numeric types that are too large for the values you are storing. For more information, see CQL data types.

BLOBs

The blob type stores binary data as Binary Large Objects (BLOBs) of arbitrary bytes (no validation). Values are hexadecimal constants in the format 0xhex or 0Xhex where hex is one or more hex characters ([0-9,a-f,A-F]). For example, 0x0000cafe3.

BLOBs are suitable for small binary payloads (small images or short strings). Storing large BLOBs, such as images or videos, can lead to unbalanced partitions and degraded performance. Therefore, the practical limit and recommended size for BLOBs is less than 1 MB, despite the maximum theoretical size of 2 GB.

For large binary content, store the data in an object store or content delivery network (CDN), and then store the URI in your database instead.

You can use functions to convert between native types and BLOBs:

  • typeAsBlob(value): Accepts a native type value and converts it into a BLOB.

  • blobAsType(value): Accepts a 64-bit BLOB value and attempts to convert it into the specified native type. Replace Type with the desired type, such as blobAsBigint.

The following example shows the use of a blob column and the bigintAsBlob function:

  1. Create a table with a blob column:

    CREATE TABLE IF NOT EXISTS cycling.lastname_bio (
  lastname varchar PRIMARY KEY,
  bio blob
);

  2. Insert data into the table:

    INSERT INTO cycling.lastname_bio (
  lastname, bio
) VALUES (
  'TSATEVICH', bigintAsBlob(3)
);

  3. Query the table:

    SELECT * FROM cycling.lastname_bio;
    Result
     lastname  | bio
-----------+--------------------
 TSATEVICH | 0x0000000000000003

(1 rows)

The following example shows the use of blobAsBigint function:

  1. Alter the table to add a bigint column:

    ALTER TABLE cycling.lastname_bio
  ADD id bigint;

  2. Insert additional data into the table:

    INSERT INTO cycling.lastname_bio (
  lastname, id
) VALUES (
  'DUVAL', blobAsBigint(0x0000000000000003)
);

  3. Query the table:

    SELECT * FROM cycling.lastname_bio;
    Result
     lastname  | bio                | id
-----------+--------------------+------
     DUVAL |               null |    3
 TSATEVICH | 0x0000000000000003 | null

(2 rows)

Collections (maps, lists, sets)

Collection types (maps, lists, and sets) are useful for storing a grouping of data within a single column, such as addresses as a collection of street, city, state, and zip code. For more information and examples, see Collections in CQL.

Counters

The counter type is used exclusively to store numbers that are updated by increments or decrements, such as page views, games played, or units in inventory. Counter values are 64-bit signed integers that can only be incremented or decremented.

counter columns have the following limitations:

  • You cannot directly set the value of a counter. Instead, you must increment or decrement counter values using the UPDATE command with the + or - operator. This includes the first write to a counter column and all subsequent updates.

  • A counter column cannot be part of a PRIMARY KEY.

  • Tables with counter columns can have only counter columns and PRIMARY KEY columns.

  • You cannot create indexes on counter columns.

  • counter columns must be set when creating a table. You cannot use ALTER TABLE to add a counter to an existing table. If you drop a counter column; you cannot use ALTER TABLE to recreate the dropped column.

  • You cannot set counter values to expire with Time-To-Live (TTL) or USING TIMESTAMP.

  • If nodes go down or writes fail, counter values can be inaccurate because counter operations are non-idempotent and cannot be retried implicitly. Retrying can result in an overcount, but not retrying can result in an undercount, so additional logic must be defined to handle counter write failures and data reconciliation.

Counter-related settings can be set in cassandra.yaml.

The following example creates a table with a counter column, and then increments and decrements the value using the UPDATE command with + and - operators:

  1. Create a table that includes only the primary key (one or more columns) and one or more counter columns:

    CREATE TABLE IF NOT EXISTS cycling.popular_count (
  id UUID PRIMARY KEY,
  popularity counter
);
    Result
    CREATE TABLE cycling.popular_count (
    id uuid PRIMARY KEY,
    popularity counter
) WITH additional_write_policy = '99PERCENTILE'
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND nodesync = {'enabled': 'true', 'incremental': 'true'}
    AND read_repair = 'BLOCKING'
    AND speculative_retry = '99PERCENTILE';

  2. Use a BATCH statement to change the value of the popularity column. Operations are applied sequentially.

    • Increment by 1

    • Increment by 125

    • Decrement by 64

    BEGIN COUNTER BATCH

  UPDATE cycling.popular_count
  SET popularity = popularity + 1
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

  UPDATE cycling.popular_count
  SET popularity = popularity + 125
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

  UPDATE cycling.popular_count
  SET popularity = popularity - 64
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

APPLY BATCH;

  3. Use a single UPDATE command to increment the value of the popularity column by 2, and use the WHERE clause to specify the row to update.

    UPDATE cycling.popular_count SET popularity = popularity + 2
  WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

  4. Check the value of the counter column after all previous updates:

    SELECT * FROM cycling.popular_count;

    The current value is 64, which is the result of the sequential increments and decrements (0 + 1 + 125 - 64 + 2):

    Result
     id                                   | popularity
--------------------------------------+------------
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |         64

(1 rows)

Static

Static columns apply the same value to all rows in a partition. The columns is static in every partition, but each partition can have a different value that is applied to the rows in that specific partition.

The table must have clustering columns to define static columns. Only non-clustering columns can be marked static, and a static column cannot be part of the partition key.

  1. Create a table cycling.country_flag with a primary key consisting of the columns country and cyclist_name. The flag column is defined as a STATIC column.

    CREATE TABLE IF NOT EXISTS cycling.country_flag (
  country text,
  cyclist_name text,
  flag int STATIC,
  PRIMARY KEY (country, cyclist_name)
);

  2. Insert some data into the table.

    INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'Belgium', 'Jacques', 1
);

INSERT INTO cycling.country_flag (
  country, cyclist_name
) VALUES (
  'Belgium', 'Andre'
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'Andre', 2
);

INSERT INTO cycling.country_flag (
  country, cyclist_name, flag
) VALUES (
  'France', 'George', 3
);

    Note that the flag column is entered with the same value for each row in the partition.

  3. Query the table to see the data.

    SELECT * FROM cycling.country_flag;
    Result
     country | cyclist_name | flag
---------+--------------+------
 Belgium |        Andre |    1
 Belgium |      Jacques |    1
  France |        Andre |    3
  France |       George |    3

(4 rows)

A batch update can be used to update the static column value for all rows in the partition.

For tables that use static columns, the unique partition key identifiers for rows can be retrieved using the DISTINCT keyword.

Use the DISTINCT keyword to select static columns. In this case, the database retrieves only the beginning (static column) of the partition.

SELECT DISTINCT country FROM cycling.country_flag;
Result
 country
---------
 Belgium
  France

(2 rows)

Tuples

The tuple type can store two or more values of the same or different data types in the same column. They are similar to collections and user-defined types (UDTs), but tuples are less strict than collections, and they don’t require defining a custom type at the keyspace level. However, DataStax recommends collections and UDTs over tuples due to the following limitations:

  • Tuples are always frozen, even if you don’t specify the frozen keyword. This means the entire field is overwritten when using INSERT or UPDATE. Therefore, your statements must provide a value for each element in the tuple, and you must explicitly declare null for elements that have no value.

  • You must access elements by position. This can make application development more difficult because you must remember the position at which each type is used and the meaning of each position.

  • During upgrades and migrations, additional steps are required to avoid data loss associated with the tuple type.

If you choose to use tuples, limit their use to simple groupings. When the grouping becomes more complex, consider whether a collection would be more appropriate.

Tuples are fixed-length sets that can accommodate up to 32,768 fields, but a best practice is to use no more than five fields. Additionally, tuples can contain nested tuples, and they can be nested in other data types. For example, both of the following are valid: tuple<int,tuple<text,text>,boolean> and set<tuple<text,inet>>.

Example: Create a table with a tuple column and index the tuple

Create a table cycling.nation_rank with a primary key of nation and using a tuple to store the rank, cyclist name, and points total for a cyclist. In the table creation statement, use angle brackets and a comma delimiter to declare the tuple component types.

CREATE TABLE IF NOT EXISTS cycling.nation_rank (
  nation text PRIMARY KEY,
  info tuple<int, text, int>
);

The tuple column can be indexed:

CREATE CUSTOM INDEX info_idx ON cycling.nation_rank (info) USING 'StorageAttachedIndex';

A query can be run to retrieve all the data from the table:

SELECT * FROM cycling.nation_rank;
Result
 nation  | info
---------+---------------------------------
   Spain | (1, 'Alejandro VALVERDE', 9054)
 Belgium |  (3, 'Phillippe GILBERT', 6222)
  France |   (2, 'Sylvain CHAVANEL', 6339)
   Italy |   (4, 'Davide REBELLINI', 6090)

(4 rows)

If a tuple column is indexed, the data in the table can be filtered using the indexed column:

SELECT * FROM cycling.nation_rank
  WHERE info = (3, 'Phillippe GILBERT', 6222);
Result
 nation  | info
---------+--------------------------------
 Belgium | (3, 'Phillippe GILBERT', 6222)

(1 rows)

Example: Create another table with the same data, but with the rank as the primary key

The previous table cycling.nation_rank uses the nation as the primary key. It is possible to store the same data using the rank as the primary key, as shown in the following example. The example creates a table named cycling.popular using a tuple to store the country name, cyclist name, and points total for a cyclist, with rank set as the primary key.

CREATE TABLE IF NOT EXISTS cycling.popular (
  rank int PRIMARY KEY,
  cinfo tuple<text, text, int>
);

Example: Create a table using nested tuples for geographic data

Another example creates a table cycling.route using a tuple to store each waypoint location name, latitude, and longitude. Two tuples are used in the following example, with one tuple nested inside the other tuple.

CREATE TABLE IF NOT EXISTS cycling.route (
  race_id int,
  race_name text,
  point_id int,
  lat_long tuple<text, tuple<float, float>>,
  PRIMARY KEY (race_id, point_id)
);

Insert some data:

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 1, ('Onnens', (46.8444, 6.6667))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 2, ('Champagne', (46.833, 6.65))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 3, ('Novalle', (46.833, 6.6))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 4, ('Vuiteboeuf', (46.8, 6.55))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 5, ('Baulmes', (46.7833, 6.5333))
);

INSERT INTO cycling.route (
  race_id, race_name, point_id, lat_long
) VALUES (
  500, '47th Tour du Pays de Vaud', 6, ('Les Clées', (46.7222, 6.5222))
);

Query the data to retrieve all the data from the table:

SELECT * FROM cycling.route;
Result
 race_id | point_id | lat_long                         | race_name
---------+----------+----------------------------------+---------------------------
     500 |        1 |    ('Onnens', (46.8444, 6.6667)) | 47th Tour du Pays de Vaud
     500 |        2 |    ('Champagne', (46.833, 6.65)) | 47th Tour du Pays de Vaud
     500 |        3 |       ('Novalle', (46.833, 6.6)) | 47th Tour du Pays de Vaud
     500 |        4 |     ('Vuiteboeuf', (46.8, 6.55)) | 47th Tour du Pays de Vaud
     500 |        5 |   ('Baulmes', (46.7833, 6.5333)) | 47th Tour du Pays de Vaud
     500 |        6 | ('Les Clées', (46.7222, 6.5222)) | 47th Tour du Pays de Vaud

(6 rows)

Vectors

Although vector data isn’t exclusively used for vector search, the vector type is required to perform vector search on a column with CQL.

The vector type stores multiple values in a single column as an array of float32 values. Vector arrays are limited to a maximum dimension of approximately 8,000 (213) items.

For example, create a table named cycling.comments_vs with a vector column to store the embeddings for vector search:

CREATE TABLE IF NOT EXISTS cycling.comments_vs (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  comment_vector VECTOR <FLOAT, 5>,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);

Other types

For more types, such as timestamps, booleans, UUIDs, and user-defined types (UDTs), see CQL data types.

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

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: Contact IBM