# ALTER TABLE COLUMN TYPE

ALTER TABLE COLUMN TYPE SQL keyword reference documentation.

Changes the data type of an existing column in a table.

The data type of the column is altered without affecting the data already stored
in the table. However, it's important to note that altering the column type can
result in data loss or errors if the new type cannot accommodate the existing
data. Therefore, it's recommended to review the data and backup the table before
altering the column type.

:::caution

- Changing the column type may lead to data loss or errors if the new type
  cannot accommodate the existing data.

- The new data type must be compatible with the existing data in the column.

:::

## Syntax

```questdb-sql
ALTER TABLE tableName ALTER COLUMN columnName TYPE typeDef;
```

## Supported Data Types

The `ALTER TABLE COLUMN TYPE` command supports changing the column type to any
compatible data type.

## Examples

Change the data type of the column `counterparty` in the table `fx_trades` to
`VARCHAR`:

```questdb-sql
ALTER TABLE fx_trades ALTER COLUMN counterparty TYPE VARCHAR;
```

When changing the column type, ensure that the new type is compatible with the
existing data. For instance, changing a column type from STRING to DOUBLE might
result in data loss or conversion errors if the existing data contains
non-numeric values.

```questdb-sql
ALTER TABLE tableName ALTER COLUMN col_name TYPE DOUBLE;
```

It is possible to specify all the additional column type parameters, like
`CAPACITY` & `CACHE`:

```questdb-sql
ALTER TABLE fx_trades ALTER COLUMN counterparty TYPE SYMBOL CAPACITY 10000 CACHE;
```

## Available Conversions

QuestDB supports a wide range of conversions. However, certain type conversions
may lead to data precision loss (e.g., converting a `FLOAT` type to an `INT`) or
range overflow (e.g., converting a `LONG` type to an `INT`). The matrices below
depict fully compatible conversions marked with `X` and conversions that may
result in data loss marked with `L`.

Numeric types support a wide range of conversions, but many of them can result
in the data / precision loss.

| From \ To | boolean | byte | short | int | float | long | double | date | timestamp | timestamp_ns | decimal |
| --------- | ------- | ---- | ----- | --- | ----- | ---- | ------ | ---- | --------- | ------------ | ------- |
| boolean   |         | X    | X     | X   | X     | X    | X      | X    | X         | X            |         |
| byte      | L       |      | X     | X   | X     | X    | X      | X    | X         | X            | X       |
| short     | L       | L    |       | X   | X     | X    | X      | X    | X         | X            | X       |
| int       | L       | L    | L     |     | L     | X    | X      | X    | X         | X            | X       |
| float     | L       | L    | L     | L   |       | L    | X      | L    | L         | L            | L       |
| long      | L       | L    | L     | L   | L     |      | L      | X    | X         | X            | X       |
| double    | L       | L    | L     | L   | X     | L    |        | L    | L         | L            | L       |

Conversions between `TIMESTAMP`, `TIMESTAMP_NS`, and `DATE` types and numeric types are fully
supported. Timestamp values are represented in microseconds since the EPOCH, Timestamp_ns values
are represented in nanoseconds since the EPOCH,
while Date values are represented in milliseconds since the EPOCH. The EPOCH is
defined as `1970-01-01T00:00:00.000000Z`.

Additionally, when converting from `BOOLEAN` values to numerics, `false` is
represented as `0`, and `true` is represented as `1`. On the way back `0` and
`NULL` are converted to `false` and all other values converted to `true`.

| From \ To    | boolean | byte | short | int | float | long | double | date | timestamp | timestamp_ns | decimal |
| ------------ | ------- | ---- | ----- | --- | ----- | ---- | ------ | ---- | --------- | ------------ | ------- |
| date         | L       | L    | L     | L   | L     | X    | X      |      | X         | X            |         |
| timestamp    | L       | L    | L     | L   | L     | X    | X      | L    |           |              |         |
| timestamp_ns | L       | L    | L     | L   | L     | X    | X      | L    | L         |              |         |

Conversions to `SYMBOL`, `STRING` and `VARCHAR` are supported from most of the
data types.

| From \ To    | symbol | string | varchar |
| ------------ | ------ | ------ | ------- |
| boolean      | X      | X      | X       |
| byte         | X      | X      | X       |
| short        | X      | X      | X       |
| int          | X      | X      | X       |
| float        | X      | X      | X       |
| long         | X      | X      | X       |
| date         | X      | X      | X       |
| timestamp    | X      | X      | X       |
| timestamp_ns | X      | X      | X       |
| double       | X      | X      | X       |
| decimal      |        | X      | X       |
| ipv4         | X      | X      | X       |
| char         | X      | X      | X       |
| uuid         | X      | X      | X       |
| symbol       |        | X      | X       |
| string       | X      |        | X       |
| varchar      | X      | X      |         |

However conversion from `SYMBOL`, `STRING` and `VARCHAR` to other types can
result in `NULL` values for inconvertable string values.

| From \ To | boolean | byte | short | char | int | float | long | date | timestamp | timestamp_ns | double | uuid | decimal |
| --------- | ------- | ---- | ----- | ---- | --- | ----- | ---- | ---- | --------- | ------------ | ------ | ---- | ------- |
| string    | L       | L    | L     | L    | L   | L     | L    | L    | L         | L            | L      | L    | L       |
| varchar   | L       | L    | L     | L    | L   | L     | L    | L    | L         | L            | L      | L    | L       |
| symbol    | L       | L    | L     | L    | L   | L     | L    | L    | L         | L            | L      | L    |         |

When column type change results into range overflow or precision loss, the same
rules as explicit [CAST](/docs/query/sql/cast/) apply.

## Parquet partitions

`ALTER TABLE ... ALTER COLUMN ... TYPE` works on tables that contain
[Parquet partitions](/docs/query/export-parquet/#in-place-conversion). Reads
return the converted values on every query, matching the behaviour of native
partitions exactly, including `NULL` sentinels, `BYTE`/`SHORT`/`CHAR` column
tops, date and timestamp scaling, and UTF-16/UTF-8 text transcoding.

By default the conversion is **lazy**: the Parquet partitions are not rewritten
by the `ALTER`. The type change is recorded in the table metadata and applied on
the query path, where each Parquet row group is decoded once and buffered for
the rows that follow. This keeps the `ALTER` cheap regardless of how much data
is stored as Parquet.

An [out-of-order (O3)](/docs/concepts/out-of-order-data/) write that later lands
on a converted Parquet partition rewrites that partition with the new type,
materialising the conversion permanently for that partition.

:::note Eager conversions

Some conversions cannot be applied lazily. In these cases QuestDB first converts
the affected Parquet partitions back to native format, one rewrite per affected
partition, before applying the type change:

- Conversions where the source or target type is `SYMBOL`.
- Chained conversions where the on-disk Parquet type no longer matches the
  current column metadata, for example a second type change on a column that was
  already converted lazily.

Unlike the metadata-only lazy path, these partition rewrites carry an I/O cost
proportional to the amount of Parquet data in the affected partitions.

:::

## Unsupported Conversions

Converting from the type to itself is not supported.

If the column `counterparty` is of type `SYMBOL`, then the following query will
result in error, even if the capacity parameter changes:

```questdb-sql
ALTER TABLE fx_trades ALTER COLUMN counterparty TYPE SYMBOL CAPACITY 4096;
```