ALTER COLUMN to change FieldType

Databases
1

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 sqlite.org leads to:
Datatypes In SQLite

but there is nothing about changing the FieldType…

Can you confirm that ?

2

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.

3

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

4

Why are you looking there instead of:

https://www.sqlite.org/lang_altertable.html

5

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.

Then:

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