Using a counter

A counter is a special column for storing a number that is updated by increments or decrements.

To load data into a counter column, or to increase or decrease the value of the counter, use the UPDATE command. DataStax Enterprise rejects USING TIMESTAMP or USING TTL in the command to update a counter column.

Procedure

Create a table for the counter column.

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

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

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;

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

The popularity column has a value of 64.

SELECT *
FROM cycling.popular_count;

Output:

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

(1 rows)

Additional increments or decrements changes the value of the counter column.