Create a static column

A static column’s value is shared by all the rows in the table in a particular partition, and is static only within that partition. The table must define clustering columns in order to define a static column. In a table that uses clustering columns, only non-clustering columns can be declared static in the table definition. To be clear - a static column cannot be part of the partition key.

Prerequisite

Create a table with a static column

  1. Create a table cycling.country_flag with a primary key consisting of the columns country and cyclist_name. The flag column is defined as a STATIC column.

    CREATE TABLE IF NOT EXISTS cycling.country_flag (
      country text,
      cyclist_name text,
      flag int STATIC,
      PRIMARY KEY (country, cyclist_name)
    );
  2. Insert some data into the table.

    INSERT INTO cycling.country_flag (
      country, cyclist_name, flag
    ) VALUES (
      'Belgium', 'Jacques', 1
    );
    
    INSERT INTO cycling.country_flag (
      country, cyclist_name
    ) VALUES (
      'Belgium', 'Andre'
    );
    
    INSERT INTO cycling.country_flag (
      country, cyclist_name, flag
    ) VALUES (
      'France', 'Andre', 2
    );
    
    INSERT INTO cycling.country_flag (
      country, cyclist_name, flag
    ) VALUES (
      'France', 'George', 3
    );

    Note that the flag column is entered with the same value for each row in the partition.

  3. Query the table to see the data.

    SELECT * FROM cycling.country_flag;

A batch update can be used to update the static column value for all rows in the partition.

For tables that use static columns, the unique partition key identifiers for rows can be retrieved using the DISTINCT keyword.

Use the DISTINCT keyword to select static columns. In this case, the database retrieves only the beginning (static column) of the partition.

SELECT DISTINCT country FROM cycling.country_flag;
Results

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