If you do a .dump of your table you will see that your 'true' and 'false' are
exactly that -- the strings (and not the boolean values). So yes, there's only
one row that has a value 0. The other rows have 1, 'true', and 'false'.
As the web page says, use 1 or 0 for true/false. It won't convert by itself as
all columns are actually typeless and the value types inserted are up to you.
So even though you may declare a columns as "int" you can still insert a string
into it. You do get some conversion in certain situations:
CREATE TABLE t(i int);
INSERT INTO "t" VALUES(1);
INSERT INTO "t" VALUES('1'); <<< this will convert to numeric one
INSERT INTO "t" VALUES('blah');
sqlite> select * from t;
1
1
blah
So you can do this
create table b(truth bool);
insert into b values(1);
insert into b values('1');
insert into b values(0);
insert into b values('0');
They all work as the string values will convert to numeric.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: [email protected] [[email protected]] on
behalf of Zygmunt Ptak [[email protected]]
Sent: Wednesday, January 25, 2012 9:10 AM
To: [email protected]
Subject: EXT :[sqlite] sqlite and boolean type (some problem)
Hi everybody,
there is some strange behaviour in sqlite.
I create table with boolean type and everything is fine.
I can add some rows, and it's OK.
sqlite> create table t(val boolean);
sqlite> insert into t values(0);
sqlite> insert into t values(1);
sqlite> insert into t values('true');
sqlite> insert into t values('false');
sqlite> insert into t values(false);
Error: no such column: false
sqlite> insert into t values(true);
Error: no such column: true
So OK, only I can't use: false, and true.
But when I try do it this:
sqlite> select * from t where val = 0;
I get only rows with val == 0.
Why sqlite doesn't show rows with value 0 or, false?
Why sqlite cast this values to one single representation?
On webpage: http://www.sqlite.org/datatype3.html is:
1.1 Boolean Datatype
SQLite does not have a separate Boolean storage class.
Instead, Boolean values are stored as integers 0 (false)
and 1 (true).
That say to me: "You can use false and/as 0, or true and/as 1".
But I get different results, when I use this...
This is so wrong, because I was a lot of problems to find an issue in my
program.
PS: I use sqlite with version (in ArchLinux):
3.7.10 2012-01-16 13:28:40 ebd01a8deffb5024a5d7494eef800d2366d97204
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
