ALTER COLUMN to change FieldType

Apparently, this is not implemented (yet ?) in SQLite:

FieldType cannot be changed.

My case is specific because I do not have entered data into my .sqlite file, so it is easy to change that (delete the file, rebuid the .sqlite with the correct FieldTypes)…

A Google on leads to:
Datatypes In SQLite

but there is nothing about changing the FieldType…

Can you confirm that ?

Correct. Sqlite implements a subset of SQL. You cannot change the column type directly. You must copy the contents of the table into a temporary table, drop the original table, recreate the original table with the new field type, and copy the contents back in.

Or in my case, just delete the file and create a new one with the FieldTypes I want. :ukraine:

Why are you looking there instead of:

Just beware of the order. :slight_smile: In any kind of such operations you must stop any parallel operations, you must be in exclusive access mode to the DB.


Rename my_table to my_table_temp
Create the new my_table with the new structure
Copy the data from my_table_temp to the new my_table
Drop my_table_temp

Alternatively, since Xojo 2021R3.1:

PRAGMA foreign_keys = OFF;
PRAGMA legacy_alter_table = ON; – Don’t change references to the columns (like when renaming them)
Rename the column my_column to my_column_temp
Add a new column my_column back with the new type / features
Set my_column values to the proper values (maybe reading/converting my_column_temp)
Drop the column my_column_temp