.import error

(1) By anonymous on 2024-03-11 00:17:52 [link] [source]

Steps to reproduce:

$ sqlite3 db1.db
sqlite> create table t1 (a text, b text, c as (a || b));
sqlite> .exit

$ echo 'aaa|bbb' > 1.txt

$ sqlite3 db1.db
sqlite> .import 1.txt t1
Error: table t1 has 2 columns but 3 values were supplied

(2.1) By Benoît Mortgat (mortgat) on 2024-12-09 09:57:06 edited from 2.0 in reply to 1 [source]

Hello

The fix, introduced in check-in 95a9c88b258f18ac (https://sqlite.org/src/info/95a9c88b258f18ac), broke a use case of mine:

sqlite> CREATE TABLE final (a TEXT, b TEXT);

sqlite> CREATE VIEW shadow (full_line) AS SELECT NULL full_line WHERE NULL IS NOT NULL;

sqlite> CREATE TRIGGER reformat INSTEAD OF INSERT ON shadow FOR EACH ROW
   ...> BEGIN
   ...>     INSERT INTO final (a, b)
   ...>     VALUES (SUBSTR(full_line, 1, 5), SUBSTR(full_line, 8));
   ...> END;

sqlite> .separator "\001"

sqlite> .import 'my_file.txt' shadow

CREATE TABLE "main"."shadow"("whatever content there is" TEXT) failed: view "shadow" already exists

Previous versions did work with views and INSTEAD OF triggers.

(3) By Harald Hanche-Olsen (hanche) on 2024-12-09 18:10:28 in reply to 2.1 [link] [source]

The inability to import into a view was previously discussed here.

Since nothing more came of the discussion, I have assumed that no fix would be forthcoming, and have adjusted my own use cases correspondingly.

Basically, instead of importing into a view i import into an auxiliary table with an AFTER INSERT trigger on it writing to a different table. Then, after importing, I truncate the auxiliary table.