Create a composite row key in GridDB using NewSQL

Question

How to create a composite row key in GridDB using NewSQL?

I am creating a collection container for storing thermostat data for multiple devices. I want to have the composite key on device_id and device_ts. I wanted to use a timeseries but it does not allow multiple keys. But even when using it with a collection container I am unable to add a composite key. Please let me know the syntax for adding a composite row key in GridDB using SQL. I found a link but its using Java programming: https://www.toshiba-sol.co.jp/en/pro/griddb/docs-en/v5/GridDB_ProgrammingGuide.html#handling-composite-row-keys-1

Current syntax used but gives error:

CREATE TABLE thermostat_data (
   device_ts TIMESTAMP NOT NULL,
   device_id STRING NOT NULL,
   temperature DOUBLE,
   humidity DOUBLE,
   target_temperature DOUBLE,
   status STRING,
   PRIMARY KEY (device_id, device_ts)
) WITH (
   expiration_type='PARTITION',
   expiration_time=30,
   expiration_time_unit='DAY'
) PARTITION BY RANGE (device_ts) EVERY (1, DAY);

The error is

Execute SQL failed (reason=CREATE TABLE failed (reason=Invalid primary key column definition)

I referred to the doc link https://griddb.org/docs-en/manuals/GridDB_SQL_Reference.html#create-table and used the same syntax for primary key but still it failed:

Syntax provided in the link: CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition …] [, PRIMARY KEY(column name [, …])] ) [WITH (property_key=property_value)] PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division_count;

Dale K · Accepted Answer · 2025-09-26 06:39:49Z

As per the documentation:

The composite primary key must be set to the columns which are continuous from the first column and can be set up to 16 columns.

This means:

the first column of your multi-column primary key must be the first column in your table definition
all addition columns of your multi-column primary key must be declared in the same order as in your table definition
all columns of your multi-column primary key must be continuous in your table definition i.e. you cannot skip any columns between the first and last column
and you may use up to a maximum of 16 columns

Therefore you need to define your index as follows:

CREATE TABLE thermostat_data (
   device_ts TIMESTAMP NOT NULL,
   device_id STRING NOT NULL,
   temperature DOUBLE,
   humidity DOUBLE,
   target_temperature DOUBLE,
   status STRING,
   PRIMARY KEY (device_ts, device_id)
) WITH (
   expiration_type='PARTITION',
   expiration_time=30,
   expiration_time_unit='DAY'
) PARTITION BY RANGE (device_ts) EVERY (1, DAY);

Collectives™ on Stack Overflow

Create a composite row key in GridDB using NewSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related