The "Error: near line 10: column val is not unique" output is
unexpected when loading the created .dump file.
This session demonstrates the issue:
$ /tmp/sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> CREATE TABLE test (
...> val DOUBLE PRECISION PRIMARY KEY NOT NULL
...> );
sqlite> INSERT INTO test VALUES (1.000000000001);
sqlite> INSERT INTO test VALUES (1.0000000000001);
sqlite> INSERT INTO test VALUES (1.00000000000001);
sqlite> INSERT INTO test VALUES (1.000000000000001);
sqlite> INSERT INTO test VALUES (1.0000000000000001);
sqlite> SELECT * FROM test;
1.000000000001
1.0000000000001
1.00000000000001
1.0
1.0
sqlite> .output test_dump.txt
sqlite> .dump test
sqlite> .quit
$ cat test_dump.txt
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test (
val DOUBLE PRECISION PRIMARY KEY NOT NULL
);
INSERT INTO test VALUES(1.000000000001);
INSERT INTO test VALUES(1.0000000000001);
INSERT INTO test VALUES(1.00000000000001);
INSERT INTO test VALUES(1.0);
INSERT INTO test VALUES(1.0);
COMMIT;
$ /tmp/sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> .read test_dump.txt
Error: near line 10: column val is not unique
sqlite> .schema
CREATE TABLE test (
val DOUBLE PRECISION PRIMARY KEY NOT NULL
);
sqlite> SELECT * FROM test;
1.000000000001
1.0000000000001
1.00000000000001
1.0
sqlite> .quit
$
NOTES:
1) Related ticket http://www.sqlite.org/src/tktview?name=1248e6cda8
2) Related sqlite-users thread
http://thread.gmane.org/gmane.comp.db.sqlite.general/68689/focus=68691
3) The source files containing the 15g formats are vdbemem.c (approx.
line 225) and vdbetrace.c (approx. line 124)
4) Referenced article (see #1) recommending 17g:
http://speleotrove.com/decimal/decifaq6.html#binapprox
5) SQLite3 documentation mentioning 15 decimal digits:
http://www.sqlite.org/datatype3.html
6) PostgreSQL behaves similarly in the psql interface, but PostgreSQL
dumps are done using a separate pg_dump utility and that seems to use
17g for the dumps so its dumps are able to faithfully recreate the
dumped database.
Is there some way to modify only the SQLite3 shell's .dump utility to
use 17g when dumping IEEE754 values (leaving all the other behavior
unchanged)?
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
