ALTER TABLE COLUMN TYPE
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.
-
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
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:
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.
ALTER TABLE tableName ALTER COLUMN col_name TYPE DOUBLE;
It is possible to specify all the additional column type parameters, like
CAPACITY & CACHE:
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 apply.
Parquet partitions
ALTER TABLE ... ALTER COLUMN ... TYPE works on tables that contain
Parquet partitions. 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) write that later lands on a converted Parquet partition rewrites that partition with the new type, materialising the conversion permanently for that partition.
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:
ALTER TABLE fx_trades ALTER COLUMN counterparty TYPE SYMBOL CAPACITY 4096;