Creating columns with a single value (static column)

In a table that uses clustering columns, non-clustering columns can be declared static in the table definition.

Static column values are shared among the rows in the partition. In a table that uses clustering columns, non-clustering columns can be declared static in the table definition. Static columns are only static within a given partition.

In the following example, the flag column is static:

CREATE TABLE IF NOT EXISTS cycling.country_flag (
  country text,
  cyclist_name text,
  flag int STATIC,
  PRIMARY KEY (country, cyclist_name)
);

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

SELECT *
FROM cycling.country_flag;

Output:

 country | cyclist_name | flag
---------+--------------+------
 Belgium |        Andre |    1
 Belgium |      Jacques |    1
  France |        Andre |    3
  France |       George |    3

(4 rows)

You can batch conditional updates to a static column.

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