CQL 3 Cassandra 1.2 counter: how to insert primary key?

Question

I want to use a Cassandra counters in CQL3 like this

create table count1 (id int PRIMARY KEY , c1 counter );

I know that in order to update the counter I need to do something of the form:

update count1 set c1 = c1+1 where ..

but before this I need to insert a value in my key (id); but I get:

cqlsh:test2> insert into count1 (id) values (4);
Bad Request: INSERT statement are not allowed on counter tables, use UPDATE instead

what gives ?

Answer 1

Cassandra doesn't have a concept of a primary key existing or not - it is only the cells that exist with a certain primary key.

So for normal column families (i.e. non-counter) you can insert or update (they are semantically identical) whether or not anything was previously inserted with that primary key.

As for counter tables, CQL requires you to use update rather than insert, to be clear that it is incrementing rather than setting the value. You cannot set a counter value in Cassandra, only inc/dec. If there was no previous counter, it is assumed to have value 0. So you can run

update count1 set c1 = c1 + 1 where id = 2;

and the counter will have value 1:

select * from count1;

 id | c1
----+----
  2 |  1