---
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

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`](/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. **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`](/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).