Insert boolean value into sqlite table

Question

I created a table MYTABLE

CREATE TABLE "MYTABLE" (
       "surname" VARCHAR,
       "name" VARCHAR,
       "id" INTEGER PRIMARY KEY  NOT NULL ,
       "flag" BOOL);

when I insert a record with:

INSERT INTO "MYTABLE" VALUES ("Super","Mario","94", true);

I get an error message, that no such column: true. If I use this:

INSERT INTO "MYTABLE" VALUES ("Super","Mario","94", "true");

I don't get any error, but when i read that record with rs.getBoolean("flag") I get false.

Finally, i tried this

INSERT INTO "MYTABLE" VALUES ("Super","Mario","94", 1);

the rs.getBoolean("flag") returns true. So the lesson here is that the boolean values in Sqlite are inserted with 0/1 ?

Yes, both in SQLite and other databases (eg SQL Server). BOOL is essentially a BIT, not a true Boolean. In Oracle even that name is missing and `NUMBER(1)` is used instead — Panagiotis Kanavos, Oct 12 '15 at 08:14
Σ' ευχαριστώ @PanagiotisKanavos ! If you want post it as an answer to take the credits — yaylitzis, Oct 12 '15 at 08:18

Osama Aftab · Answer 1 · 2019-11-21T11:29:58.477

5

SQLite does not have a separate Boolean storage class.Boolean values are stored as integers 0 and 1. source

edited Nov 21 '19 at 11:29

answered Oct 12 '15 at 08:25

Osama Aftab

1,161
9
15

4

You should make it clear the text of this answer is copied from [SQLite's page on datatypes](https://www.sqlite.org/datatype3.html), otherwise it may be considered plagiarism – Panagiotis Kanavos Oct 12 '15 at 08:29
2

"Plagiarism" in respect to 16 words feels like an overstatement. But the source link is helpful. – mxmlnkn Nov 21 '19 at 11:22

score 3 · Accepted Answer · answered Oct 12 '15 at 08:27

3

Yes, the BOOL type is synonymous to a BIT in many databases, including SQLite and SQL Server. Other databases, like Oracle, do not even have a boolean type and a NUMBER(1) field is used to store boolean values by convention.

answered Oct 12 '15 at 08:27

Panagiotis Kanavos

120,703
13
188
236

Insert boolean value into sqlite table

2 Answers2