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
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 age
in the table employees
to INTEGER
:
ALTER TABLE employees ALTER COLUMN age TYPE INTEGER;
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 tbl ALTER COLUMN col_name TYPE DOUBLE;
It is possible to specify all the additional column type parameters, like
CAPACITY
& CACHE
:
ALTER TABLE tbl ALTER COLUMN department 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 |
---|---|---|---|---|---|---|---|---|---|
boolean | X | X | X | X | X | X | X | X | |
byte | L | X | X | X | X | X | X | X | |
short | L | L | X | X | X | X | X | X | |
int | L | L | L | L | X | X | X | X | |
float | L | L | L | L | L | X | L | L | |
long | L | L | L | L | L | L | X | X | |
double | L | L | L | L | X | L | L | L |
Conversions between TIMESTAMP
and DATE
types and numeric types are fully
supported. Timestamp values are represented in microseconds 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 |
---|---|---|---|---|---|---|---|---|---|
date | L | L | L | L | L | X | X | X | |
timestamp | L | L | L | L | L | X | X | 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 |
double | X | 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 | double | uuid |
---|---|---|---|---|---|---|---|---|---|---|---|
string | L | L | L | L | L | L | L | L | L | L | L |
varchar | L | L | L | L | L | L | L | L | L | L | L |
symbol | 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.
Unsupported Conversions
Converting from the type to itself is not supported.
If the column department
is of type SYMBOL
, then the following query will
result in error, even if the capacity parameter changes:
ALTER TABLE employees ALTER COLUMN department TYPE SYMBOL CAPACITY 4096;