Single index match on a column with options

This example uses the following table and indexes:

CREATE TABLE IF NOT EXISTS cycling.comments_vs (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  comment_vector VECTOR <FLOAT, 5>,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);
CREATE CUSTOM INDEX commenter_cs_idx ON cycling.comments_vs (commenter)
USING 'StorageAttachedIndex' 
WITH OPTIONS = {'case_sensitive': 'true', 'normalize': 'true', 'ascii': 'true'};

Case-sensitivty

The column commenter is not the partition key in this table, so an index is required to query on it. If we want to check commenter as a case-sensitive value, we can use the case_sensitive option set to true.

Note that no results are returned if you use an inappropriately case-sensitive value in the query:

  • Query

  • Result

SELECT * FROM comments_vs WHERE commenter ='alex';
 id | created_at | comment | comment_vector | commenter | record_id
----+------------+---------+----------------+-----------+-----------

(0 rows)

When we switch the case of the cyclist’s name to match the case in the index, the query succeeds:

  • Query

  • Result

SELECT comment,commenter FROM comments_vs WHERE commenter ='Alex';
 comment                                | commenter
----------------------------------------+-----------
 LATE RIDERS SHOULD NOT DELAY THE START |      Alex
      Second rest stop was out of water |      Alex
(2 rows)

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com