Alter and update table schemas

Schema changes on a hypertable use normal PostgreSQL ALTER TABLE syntax, and TimescaleDB propagates those changes to every underlying chunk.

Note

While you can change the schema of an existing hypertable, you cannot change the schema of a continuous aggregate. For continuous aggregates, 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.

Altering hypertables with columnstore enabled

Most common schema modifications work on hypertables with columnstore 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 columnstore policy
2. Convert the affected chunks back into rowstore
3. Disable columnstore
4. Perform the schema change
5. Re-enable columnstore and restart the policy

Example: change the column type on a hypertable with columnstore enabled

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

1. Check if you have a columnstore policy and note its settings

   Query the jobs catalog to find the job_id and configuration for any existing columnstore policy:

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

2. Pause the columnstore policy

   If a policy exists, pause it so it does not convert chunks while you are modifying the schema:

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

3. Convert all chunks back to rowstore

   Use convert_to_rowstore to move each chunk out of the columnstore:

   DO $$ DECLARE chunk regclass;
   BEGIN
     FOR chunk IN SELECT show_chunks('conditions') LOOP
       CALL convert_to_rowstore(chunk);
     END LOOP;
   END $$;

4. Disable columnstore

   Some operations, such as changing a column type, require columnstore to be fully disabled:

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

5. Perform the schema modification

   ALTER TABLE conditions
     ALTER COLUMN temperature TYPE double precision;

6. Re-enable columnstore with original settings

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

7. Restart the columnstore policy

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

8. Optionally, convert chunks to columnstore immediately

   Without this step, chunks are converted by the policy on its next scheduled run. To convert them now, use convert_to_columnstore:

   DO $$ DECLARE chunk regclass;
   BEGIN
     FOR chunk IN SELECT show_chunks('conditions') LOOP
       CALL convert_to_columnstore(chunk);
     END LOOP;
   END $$;

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