> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

export const PG = 'Postgres';

export const ROWSTORE = 'rowstore';

export const COLUMNSTORE = 'columnstore';

export const CAGG = 'continuous aggregate';

export const CHUNK = 'chunk';

export const HYPERTABLE = 'hypertable';

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

<Info>
  While you can change the schema of an existing {HYPERTABLE}, you cannot change the schema of a {CAGG}. For {CAGG}s,
  the only permissible changes are renaming a view, setting a schema, changing the owner, and adjusting other
  parameters.
</Info>

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

```sql theme={"dark"}
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:

```sql theme={"dark"}
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 {HYPERTABLE}s 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 {CHUNK}s back into {ROWSTORE}
3. Disable {COLUMNSTORE}
4. Perform the schema change
5. Re-enable {COLUMNSTORE} and restart the policy

## Change the column type on a hypertable

This example shows how to change a column's data type on a {HYPERTABLE} with {COLUMNSTORE} enabled, which requires
conversion to {ROWSTORE}:

1. **Create a hypertable with columnstore enabled**

   ```sql theme={"dark"}
   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**

   ```sql theme={"dark"}
   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**

   ```sql theme={"dark"}
   SELECT job_id, config FROM timescaledb_information.jobs
   WHERE proc_name = 'policy_compression'
     AND hypertable_name = 'conditions';
   ```

4. **If a policy exists, pause it**

   ```sql theme={"dark"}
   SELECT alter_job(<job_id>, scheduled => false);
   ```

5. **Convert all chunks back to rowstore**

   ```sql theme={"dark"}
   SELECT decompress_chunk(show_chunks('conditions'));
   ```

6. **Disable columnstore**

   ```sql theme={"dark"}
   ALTER TABLE conditions SET (timescaledb.columnstore = false);
   ```

7. **Perform the schema modification**

   ```sql theme={"dark"}
   ALTER TABLE conditions
     ALTER COLUMN temperature TYPE double precision;
   ```

8. **Re-enable columnstore with original settings**

   ```sql theme={"dark"}
   ALTER TABLE conditions SET (
     timescaledb.columnstore = true,
     timescaledb.compress_orderby = 'time DESC',
     timescaledb.compress_segmentby = 'device_id'
   );
   ```

9. **Restart the columnstore policy**

   ```sql theme={"dark"}
   SELECT alter_job(<job_id>, scheduled => true);
   ```

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

    ```sql theme={"dark"}
    SELECT compress_chunk(show_chunks('conditions'));
    ```

For more information about {PG} `ALTER TABLE` operations, see the [{PG} `ALTER TABLE` documentation][postgres-alter-table].

[postgres-alter-table]: https://www.postgresql.org/docs/current/sql-altertable.html