Feedback Edit

.alter column

  • Article
  • 2 minutes to read

Alters the data type of an existing table column.

Warning

When altering the data type of a column, any pre-existing data in that column will return a null value in future queries. After using .alter column, that data cannot be recovered, even by using another command to alter the column type back to a previous value. If you need to preserve pre-existing data, see our recommended procedure for changing the type of a column without losing data.

Syntax

.alter column [DatabaseName .] TableName . ColumnName type = ColumnNewType

Example

.alter column ['Table'].['ColumnX'] type=string

Changing column type without data loss

To change column type while retaining the historical data, create a new, properly typed table.

For each table T1 you'd like to change a column type in, execute the following steps:

  1. Create a table T1_prime with the correct schema (the right column types).
  2. Swap the tables using .rename tables command, which allows swapping table names.
.rename tables T_prime=T1, T1=T_prime

When the command completes, the new data flows to T1 that is now typed correctly and the historical data is available in T1_prime.

Until T1_prime data goes out of the retention window, queries touching T1 need to be altered to perform union with T1_prime.