I’ve come across a situation where I need to incorporate an additional primary key into an existing “alarms” hyper table due to changes in requirements.
The current table has two columns, “Timestamp” and “objId” (serving as the primary key). Now, I need to add another column, “model,” as part of the primary key.
While I can drop the existing key and create a new primary key, I’m uncertain about handling scenarios involving existing compressed data. Should I decompress and recompress the data? I would appreciate your suggestions.
As an additional example, here’s a query to create an “alarms” table with the specified fields:
CREATE TABLE alarms (
Timestamp TIMESTAMP,
objId VARCHAR(255),
model VARCHAR(255),
jsonval JSON,
alarmkeys VARCHAR(255),
PRIMARY KEY (objId, model)
);
select create_hypertable('alarms ',‘timestamp’, migrate_data => TRUE, if_not_exists => TRUE);
ALTER TABLE alarms SET (timescaledb.compress, timescaledb.compress_segmentby = ‘objId’, timescaledb.compress_orderby = ‘timestamp DESC’);
This query creates a table named “alarms” with the specified fields, and it designates a composite primary key using both “objId” and “model.”
Please suggest.