Alter and update table schemas

Modify the schema of an existing hypertable with the ALTER TABLE command. See examples for adding a table and checking the schema before applying it

To modify the schema of an existing , you can use the ALTER TABLE command. When you change the schema, the changes are also propagated to each underlying .

While you can change the schema of an existing , you cannot change the schema of a . For s, the only permissible changes are renaming a view, setting a schema, changing the owner, and adjusting other parameters.

For example, to add a new column called address to a table called distributors:

ALTER TABLE distributors
  ADD COLUMN address varchar(30);

This creates the new column, with all existing entries recording NULL for the new column.

Changing the schema can, in some cases, consume a lot of resources. This is especially true if it requires underlying data to be rewritten. If you want to check your schema change before you apply it, you can use a CHECK constraint, like this:

ALTER TABLE distributors
  ADD CONSTRAINT zipchk
  CHECK (char_length(zipcode) = 5);

This scans the table to verify that existing rows meet the constraint, but does not require a table rewrite.

Alter hypertables with columnstore enabled

Most common schema modifications work on s with enabled, including adding columns, renaming columns, dropping columns, adding constraints, setting NOT NULL, and changing defaults. However, some operations are blocked, the most common of them being:

• Changing column data type (ALTER COLUMN ... TYPE)
• Changing column storage (ALTER COLUMN ... SET STORAGE)
• Dropping orderby or segmentby columns
• Row-level security operations (ENABLE/DISABLE ROW SECURITY)

When you attempt a blocked operation, you receive an error:

ERROR: operation not supported on hypertables that have columnstore enabled

If you encounter this error, you need to:

1. Stop any policy
2. Convert the affected s back into
3. Disable
4. Perform the schema change
5. Re-enable and restart the policy

Change the column type on a hypertable

This example shows how to change a column’s data type on a with enabled, which requires conversion to :

1. Create a hypertable with columnstore enabled

   CREATE TABLE conditions (
     time TIMESTAMPTZ NOT NULL,
     device_id INT NOT NULL,
     temperature FLOAT NOT NULL,
     humidity FLOAT
   ) WITH (
     timescaledb.hypertable,
     timescaledb.columnstore = true,
     timescaledb.compress_orderby = 'time DESC',
     timescaledb.compress_segmentby = 'device_id'
   );
   

2. (Optional) Insert sample data

   INSERT INTO conditions
   VALUES
     (NOW(), 1, 72.5, 65.2),
     (NOW(), 2, 68.3, 70.1);
   

3. Check for a columnstore policy and note its settings

   SELECT job_id, config FROM timescaledb_information.jobs
   WHERE proc_name = 'policy_compression'
     AND hypertable_name = 'conditions';
   

4. If a policy exists, pause it

   SELECT alter_job(<job_id>, scheduled => false);
   

5. Convert all chunks back to rowstore

   SELECT decompress_chunk(show_chunks('conditions'));
   

6. Disable columnstore

   ALTER TABLE conditions SET (timescaledb.columnstore = false);
   

7. Perform the schema modification

   ALTER TABLE conditions
     ALTER COLUMN temperature TYPE double precision;
   

8. Re-enable columnstore with original settings

   ALTER TABLE conditions SET (
     timescaledb.columnstore = true,
     timescaledb.compress_orderby = 'time DESC',
     timescaledb.compress_segmentby = 'device_id'
   );
   

9. Restart the columnstore policy

   SELECT alter_job(<job_id>, scheduled => true);
   

10. (Optional) Manually convert the chunks to columnstore immediately

    SELECT compress_chunk(show_chunks('conditions'));
    

For more information about ALTER TABLE operations, see the ALTER TABLE documentation.