IMPORT [FILE] into [VIEW]

(1) By anonymous on 2024-05-23 13:57:20 [link] [source]

There was a behavior change to the file import process between versions 3450100 and 3450300.

Instead of importing data into the view, and thus by means of an instead-of-insert trigger into an underlying target table with an auto-increment ID column, it now results in - ERROR table already exists.

Yes, there is a work-around using an initial secondary table that does not contain the auto-increment ID column, followed by subsequent column-specific inserts into the primary target table; however, that diminishes the efficiency of the file import process.

(2) By Stephan Beal (stephan) on 2024-05-23 14:04:34 in reply to 1 [link] [source]

Instead of importing data into the view, and thus by means of an instead-of-insert trigger into an underlying target table with an auto-increment ID column, it now results in - ERROR table already exists.

Tip: demonstrating the problem with SQL or C, rather than describing it in high-level terms, will go a long way towards getting it resolved.

(3) By Bo Lindbergh (_blgl_) on 2024-05-23 15:10:27 in reply to 2 [link] [source]

I think anon is complaining about check-in 95a9c88b258f18ac using sqlite3_table_column_metadata to check whether the destination exists. Unlike the previous method, sqlite3_table_column_metadata doesn't work on views.

(5) By Harald Hanche-Olsen (hanche) on 2024-05-23 21:58:53 in reply to 3 [source]

Aha. To make it explicit:

With test.csv being the one-line file containing 5,3 run this code:

create table xy (x,y);
create view uv as select x+y u, x-y v from xy;
create trigger uv
instead of insert on uv
begin
  insert into xy
  values ((new.u+new.v)/2.0,(new.u-new.v)/2.0);
end;
.import -csv test.csv uv
select * from xy;

Previously, this prodoced the output 4.0|1.0, but now, this is the result:

(null) failed:
view "uv" already exists

(4) By anonymous on 2024-05-23 20:32:27 in reply to 1 [link] [source]

Importing files into views (with a INSTEAD OF INSERT trigger) is something that I had done before too and I also find it useful. Due to this, I suppose that I am not the only one.

(6) By Felix Dietze (fdietze) on 2024-05-27 21:28:30 in reply to 1 [link] [source]

Here is a reproduction: https://github.com/fdietze/sqlite-trigger-import-bug/actions/runs/9260379267/job/25474037576#step:4:29

It succeeds for sqlite 3.45.1 and fails for 3.45.2.