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

Keyspace must exist

Create a table with a static column

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)
);

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.

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

Create a static column

Prerequisite

Create a table with a static column

Was this helpful?

Give Feedback