INSERT + SELECT data type mismatch on similar fields

Question

I'm running the following SQLite workaround to add a primary key to a table that did not have one. I am getting a datatype mismatch on

INSERT INTO cities 
SELECT id, name FROM old_cities;

However, the fields have exactly the same type. Is it possible that his happens due to running the queries from DbBrowser for SQLite?

CREATE table cities (
    id INTEGER NOT NULL,
    name TEXT NOT NULL
);

INSERT INTO cities (id, name)
VALUES ('pan', 'doul');

END TRANSACTION;
PRAGMA foreign_keys=off;
BEGIN TRANSACTION;

ALTER TABLE cities RENAME TO old_cities;

--CREATE TABLE cities (
--   id INTEGER NOT NULL PRIMARY KEY,
--   name TEXT NOT NULL
--);


CREATE TABLE cities (
   id INTEGER NOT NULL,
   name TEXT NOT NULL,
   PRIMARY KEY (id)
);

SELECT * FROM old_cities;

INSERT INTO cities 
SELECT id, name FROM old_cities;

DROP TABLE old_cities;

COMMIT;

score 1 · Accepted Answer · answered Aug 26 '21 at 16:19

You have defined the column id of the table cities to be INTEGER, but with this:

INSERT INTO cities (id, name) VALUES ('pan', 'doul');

you insert the string 'pan' as id.
SQLite does not do any type checking in this case and allows it.

Did you mean to insert 2 rows each having the names 'pan' and 'doul'?
If so, you should do something like:

INSERT INTO cities (id, name) VALUES (1, 'pan'), (2, 'doul');

Later you rename the table cities to old_cities and you recreate cities but you do something different: you define id as INTEGER and PRIMARY KEY.
This definition is the only one that forces type checking in SQLite.

So, when you try to insert the rows from old_cities to cities you get an error because 'pan' is not allowed in the column id as it is defined now.

INSERT + SELECT data type mismatch on similar fields

1 Answers1