0

Consider this case when the last line of the CSV is a duplicate

myFile1.csv

column1,column2,column3
r1v1,r1v2,r1v3
r2v1,r2v2,r2v3
r1v1,r1v2,r1v3

sqliteCommandsFile.sql
CREATE TABLE foo(column1 PRIMARY KEY UNIQUE,column2,column3);
.mode csv
.import myFile1.csv foo

> sqlite3 /tmp/output.db < sqliteCommandsFile.sql

INSERT failed: UNIQUE constraint failed: foo.column1

> echo $?
1

Now consider this case 

myFile2.csv

column1,column2,column3
r1v1,r1v2,r1v3
r2v1,r2v2,r2v3
r1v1,r1v2,r1v3
r3v1,r3v2,r3v3

sqliteCommandsFile2.sql
CREATE TABLE foo(column1 PRIMARY KEY UNIQUE,column2,column3);
.mode csv
.import myFile2.csv foo

> sqlite3 /tmp/output.db < sqliteCommandsFile2.sql

INSERT failed: UNIQUE constraint failed: foo.column1

> echo $?
0

I wouldn't care so much about the status code, but since sqlite3 will rollback when it has an error on the last line the database will be empty when the duplicate is on the last line.

I have a hack in place to add a random line to the end of the file but it doesn't seem reasonable to do that.

Avba
  • 14,822
  • 20
  • 92
  • 192
  • 1
    Both cases show the same insert error. Furthermore your second SQL script imports the same file as the first, `myFile1.csv` instead of `myFile2.csv`. Conclusion: Your code/screen snippets don't show the complete picture. Take the time to create a [mcve]. – Murphy Nov 02 '17 at 13:01
  • fixed so that the second one imports the file2. Both will print to sterr the same error , but notice that the exit code is 0 in the second case. – Avba Nov 05 '17 at 07:48
  • I can reproduce the behaviour here with version 3.20.1, but I don't have an explanation. I tried also removing the redundant column names and adding an explicit conflict clause. Could be a bug in the CLI. I suggest you either report a bug, or debug the sources yourself. – Murphy Nov 07 '17 at 07:38
  • quick and dirty fix is to add a dummy random line to the end and then delete it after the db is created. Very annoying – Avba Nov 07 '17 at 08:42
  • [Related SQLite bug](http://www.sqlite.org/src/tktview?name=bd770b2c52). Which version do you use? – Murphy Nov 08 '17 at 08:19
  • Tried several including 3.2 3.21 and 3.07 i believe – Avba Nov 08 '17 at 08:21

1 Answers1

0

You can force the expected behaviour by importing into a temporary table first, then moving the content into the table you created:

CREATE TABLE foo(column1 TEXT PRIMARY KEY, column2 TEXT, column3 TEXT);

.mode csv
.import import2.csv temp

insert into foo select * from temp;
drop table temp;

BTW, a primary key is unique by definition, the keyword is redundant in this place.

Still, even when .bail on is set and wrapped into a transaction, importing directly into foo doesn't bail out on the error. Import continues until the last row and returns the error status of that action, which I would consider a bug in the CLI that you should report.

Murphy
  • 3,827
  • 4
  • 21
  • 35