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

## 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;
```