Filtering on Primary Key in Cassandra

Question

I have the following table:

CREATE TABLE tab1 (id int PRIMARY KEY, price int, name text);

The following queries return errors:

SELECT name FROM tab1 WHERE id > 5;
SELECT name FROM tab1 WHERE id > 5 ALLOW FILTERING;

How can I fix it?

Answer 1

SELECT name FROM tab1 WHERE id > 5 ALLOW FILTERING; will not give an error since you are using allow filtering. If your queries require to use allow filtering, then you need to redesign your tables according to the queries. Allow filtering is not efficient way of querying your tables, especially in production. please check here

SELECT name FROM tab1 WHERE id > 5; will give you an error

[Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

The reason is; Cassandra doesn't work as how relational database works. The table structure doesn't allow you to run any query you want to, so you model your tables according to queries.

Please check here for where clause details. As it is stated in the documentation The partition key columns support only two operators: = and IN, in your case you are using greater, which causes you to get an error.