CODEWITHSUNDEEP
cassandracql
Jay
Jay

Reputation: 20136

Filtering on primary key with nullable column in cassandra versions 1.2.5 and greater

Im trying to understand how to do this, or if I cant, why cant I do this. ie

cqlsh:ps> create table s1 (a text, b text, stuff text, primary key(a,b));
cqlsh:ps> select * from s1 where a='a' and b=null;
Bad Request: Invalid null clustering key part b

It appears that there is a work around, i.e. don't store nulls in column "b", but something like "##null##", but that just seems silly.

Any help would be much appreciated.

Upvotes: 2

Views: 3297

Answers (1)

Lyuben Todorov
Lyuben Todorov

Reputation: 14153

You can't insert a null into b because it's part of the clustering key. If you try an insert query along the lines of the below:
INSERT INTO ps.s1 (a, b, c) VALUES ('a', null, 'c_val');
You should get an exception:
Bad Request: Invalid null value for clustering key part password

If you want to retrieve all data where a=a then you would simply do:

select * from s1 where a='a';

You can however insert nulls into fields which aren't part of the key:

INSERT INTO ps.s1 (a, b, c) VALUES ('a', 'b', null);

Upvotes: 3

Related Questions