Create a counter

A counter column value is a limited special column for storing a number that is updated by increments or decrements. You cannot set the value of a counter, you can only either increment or decrement it.

A table that contains a counter column must only have a primary key and one or more counter columns. A counter column cannot be part of the primary key.

There are some limitations on a counter column. You cannot create an index on a counter column. If you drop a counter column from a table, you cannot re-add it to the same table. Additionally, you cannot set a counter column’s value to expire using the Time-To-Live (TTL) or USING TIMESTAMP properties.

To load data into a counter column, or to increase or decrease the value of the counter, use the UPDATE command.

Prerequisite

Keyspace must exist

Create a table for the counter column:

CREATE TABLE IF NOT EXISTS cycling.popular_count (
  id UUID PRIMARY KEY,
  popularity counter
);

Results

Loading data into a counter column is different than other tables. The data is updated rather than inserted.

The first example uses a BATCH statement to increment the value of the popularity column by 1, then 125, and then decrements 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;

The second example increments the value of the popularity column by 2. Note the use of the WHERE clause to specify the row to update.

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

After all these updates, the popularity counter column has a value of 64.

SELECT * FROM cycling.popular_count;

Results

The operations for a counter column are straightforward; additional increments or decrements change the value of the counter column.

Create a counter

Prerequisite

Was this helpful?

Give Feedback