Counter type

A counter column value is a 64-bit signed integer. You cannot set the value of a counter, which supports two operations: increment and decrement.

Do not assign this type to a column that serves as the primary key or partition key. Also, do not use the counter type in a table that contains anything other than counter types and the primary key. To generate sequential numbers for surrogate keys, use the timeuuid type instead of the counter type. You cannot create an index on a counter column or set data in a counter column to expire using the Time-To-Live (TTL) property.

CREATE TABLE WebLogs (
    page_id uuid,
    page_name Text,
    insertion_time timestamp,
    page_count counter,
    PRIMARY KEY (page_id, insertion_time)
);
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot mix counter and non counter columns in the same table"

Counter columns do not exist with non-counter columns, so every other column in the table should be a primary key/clustering keys.

CREATE TABLE WebLogs (
    page_id uuid,
    page_name Text,
    insertion_time timestamp,
    page_count counter,
    PRIMARY KEY ((page_id,page_name), insertion_time)
);

INSERT INTO weblogs (page_id, page_name, insertion_time, page_count) values(uuid(), 'test.com', dateof(now()), 0) ;
InvalidRequest: Error from server: code=2200 [Invalid query] message="INSERT statements are not allowed on counter tables, use UPDATE instead"

UPDATE weblogs SET page_count = page_count + 1 WHERE page_id = uuid() and page_name = 'test.com' and insertion_time = '2018-06-07 12:28:47+0000';

SELECT * FROM weblogs;

 page_id                              | page_name | insertion_time                  | page_count
--------------------------------------+-----------+---------------------------------+------------
 ebaecb68-226e-4e32-b7f0-50adb19df076 |  test.com | 2018-06-07 12:28:47.000000+0000 |          1

(1 rows)

UPDATE weblogs SET page_count = page_count + 1 WHERE page_id = ebaecb68-226e-4e32-b7f0-50adb19df076 and page_name = 'test.com' and insertion_time = '2018-06-07 12:28:47+0000';

SELECT * FROM weblogs;

 page_id                              | page_name | insertion_time                  | page_count
--------------------------------------+-----------+---------------------------------+------------
 ebaecb68-226e-4e32-b7f0-50adb19df076 |  test.com | 2018-06-07 12:28:47.000000+0000 |          2

(1 rows)

UPDATE weblogs SET page_count = page_count - 1 WHERE page_id = ebaecb68-226e-4e32-b7f0-50adb19df076 and page_name = 'test.com' and insertion_time = '2018-06-07 12:28:47+0000';
UPDATE weblogs SET page_count = page_count - 1 WHERE page_id = ebaecb68-226e-4e32-b7f0-50adb19df076 and page_name = 'test.com' and insertion_time = '2018-06-07 12:28:47+0000';

SELECT * FROM weblogs;

 page_id                              | page_name | insertion_time                  | page_count
--------------------------------------+-----------+---------------------------------+------------
 ebaecb68-226e-4e32-b7f0-50adb19df076 |  test.com | 2018-06-07 12:28:47.000000+0000 |         -1

(1 rows)