Is there a way to change a column type from INTEGER to TEXT without copying of the table?

(1) By g00ds (164896512) on 2022-06-30 12:37:19 [link] [source]

Hi there! I have a large table. It contains a column of INTEGER type. The column type was used to satisfy the requirements, but now I need to store arbitrary strings in that column as well as integers, so I need to change the type of the column to TEXT. I read somewhere that SQLite doesn't support ALTER COLUMN yet. I found that I can create a second table with a changed table schema and copy the data from the first table to the second one, but it requires 2 times more space to temporarily store the table. It's difficult for me because the database size is more than 100 GB and I don't have that much free space on my disk now. It will also take a lot of time. I want to ask if there is some better and faster way to do it? Since it's weird to copy the whole table just to change the type of a single column. How do you guys deal with such problems? Thanks!

(2) By Gunter Hick (gunter_hick) on 2022-06-30 12:58:04 in reply to 1 [link] [source]

Just store text values. The declared type in SQLite serves to define the preferred representation. You can always store any type of value in any column, even if it does not match the declared type.

This could confuse some wrappers, and also any text that "looks like" a number will be stored as a number.

You could also offload the data to a csv file and then just DROP and CREATE the table with the new definition before importing the data. But this would probably require much more than double the space.

Also, are you implying that you have a single 100GB table in your database? Is that a simple key/value table? Otherwise this would suggest that maybe you have skipped the data modeling step, which will come back and bite you at every possibility.

(4) By Richard Hipp (drh) on 2022-06-30 13:22:58 in reply to 2 [link] [source]

One caveat: If the text you are trying to store looks like a number, it will be converted into a number. Depending on the application, that might not be what you want. Leading zeros will be remove, and values that are too large to fit into a 64-bit integer will be converted to floating point:

CREATE TABLE t1(a INT);
INSERT INTO t1(a) VALUES(1),('0002'),('300000000000000000000');
SELECT a FROM t1;

Results in:

┌─────────┐
│    a    │
├─────────┤
│ 1       │
│ 2       │
│ 3.0e+20 │
└─────────┘

(3.1) By curmudgeon on 2022-06-30 13:10:52 edited from 3.0 in reply to 1 [source]

sqlite> create table t(x INTEGER);
sqlite> insert into t values(1),('one');
sqlite> select * from t;
1
one

That wouldn't work if you created the table as STRICT

sqlite> create table t2(x INTEGER) STRICT;
sqlite> insert into t2 values(1),('one');
Runtime error: cannot store TEXT value in INTEGER column t2.x (19)

(5) By HashBackup (hashbackup) on 2022-07-08 16:44:27 in reply to 1 [link] [source]

You could modify your schema:

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

https://www.sqlite.org/pragma.html#pragma_writable_schema

Example:

$ sqlite3
-- Loading resources from /Users/jim/.sqliterc
SQLite version 3.38.0 2022-02-22 19:15:21 with the Encryption (see-aes128-ofb)
Copyright 2016 Hipp, Wyrick & Company, Inc.
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (i integer);
sqlite> .schema
CREATE TABLE t (i integer);
sqlite> insert into t values('000001');
sqlite> select * from t;
i
-
1
sqlite> select * from sqlite_master;
type   name  tbl_name  rootpage  sql                       
-----  ----  --------  --------  --------------------------
table  t     t         2         CREATE TABLE t (i integer)
sqlite> update sqlite_master set sql = replace(sql, 'integer', 'text');
Parse error: table sqlite_master may not be modified
sqlite> pragma writable_schema = 1;
sqlite> update sqlite_master set sql = replace(sql, 'integer', 'text');
sqlite> .schema
CREATE TABLE t (i text);
sqlite> insert into t values('000001');
sqlite> select * from t;
i
-
1
1
sqlite> pragma writable_schema = reset;
sqlite> insert into t values('000001');
sqlite> select * from t;
i     
------
1     
1     
000001
sqlite>