Schema modifications

You can modify the schema of compressed hypertables in recent versions of Timescale.

Schema modification	Before TimescaleDB 2.1	TimescaleDB 2.1 to 2.5	TimescaleDB 2.6 and above
Add a nullable column	❌	✅	✅
Add a column with a default value and a `NOT NULL` constraint	❌	❌	✅
Rename a column	❌	✅	✅
Drop a column	❌	❌	✅
Change the data type of a column	❌	❌	❌

To perform operations that aren't supported on compressed hypertables, first decompress the table.

Add a nullable column

To add a nullable column:


ALTER TABLE <hypertable> ADD COLUMN <column_name> <datatype>;

For example:


ALTER TABLE conditions ADD COLUMN device_id integer;

Note that adding constraints to the new column is not supported before TimescaleDB 2.6.

To add a column with a default value and a not-null constraint:


ALTER TABLE <hypertable> ADD COLUMN <column_name> <datatype>
    NOT NULL DEFAULT <default_value>;

For example:


ALTER TABLE conditions ADD COLUMN device_id integer
    NOT NULL DEFAULT 1;

To rename a column:


ALTER TABLE <hypertable> RENAME <column_name> TO <new_name>;

For example:


ALTER TABLE conditions RENAME device_id TO devid;

You can drop a column from a compressed hypertable, if the column is not an orderby or segmentby column. To drop a column:


ALTER TABLE <hypertable> DROP COLUMN <column_name>;

For example:


ALTER TABLE conditions DROP COLUMN temperature;