---
title: Alter and update table schemas | Tiger Data Docs
description: Modify the schema of an existing hypertable with the ALTER TABLE command in PostgreSQL
---

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

Note

Schema changes to a continuous aggregate are limited. You can rename the view or its columns, set a schema, change the owner, and adjust parameters. Since TimescaleDB 2.28.0, you can also add a new aggregate with [`ALTER MATERIALIZED VIEW ... ADD COLUMN`](/docs/reference/timescaledb/continuous-aggregates/alter_materialized_view/index.md). Other schema changes are not supported.

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`): blocked while any chunk is in the columnstore
- **Changing column storage** (`ALTER COLUMN ... SET STORAGE`): blocked while columnstore is enabled
- **Dropping orderby or segmentby columns**: blocked while columnstore is enabled
- **Row-level security operations** (`ENABLE/DISABLE ROW SECURITY`): blocked while columnstore is enabled

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

```
ERROR: operation not supported on hypertables with compressed chunks
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. For an operation blocked while columnstore is enabled, 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 every chunk to be in the 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`](/docs/reference/timescaledb/hypercore/convert_to_rowstore/index.md) 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. **Perform the schema modification**

   ```
   ALTER TABLE conditions
     ALTER COLUMN temperature TYPE double precision;
   ```

5. **Restart the columnstore policy**

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

6. **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`](/docs/reference/timescaledb/hypercore/convert_to_columnstore/index.md):

   ```
   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](https://www.postgresql.org/docs/current/sql-altertable.html).