Do you need to store a Boolean value in the SQL database? Does the data type exist? Read this article and find out.
What is a Boolean?
A Boolean is a data type that can store a value of True or False. This is often stored as 1 (true) or 0 (false). It is named after George Boole, who first defined an algebraic system of logic in the 19th century. 
Boolean values are common in programming languages, but do they exist in SQL? The
answer is that it depends on the database vendor you are using
.
The good news is that even if there is no dedicated Boolean data type, you can achieve the same functionality using other data types.
Is there a Boolean in SQL?
This table shows whether
or not there is a Boolean data type in each SQL provider:
Boolean
database?
Use instead Oracle No NUMBER(1) SQL Server No BIT MySQL No BIT or TINYINT PostgreSQL Yes Oracle Boolean Is
there a Boolean data type in Oracle SQL?
No
, there isn’t
.
You cannot declare a column with the BOOLEAN data type
.
However, there are several alternatives, which I have detailed below
.
The recommended way to store Booleans in Oracle SQL is to use a NUMBER(1) field. This can store 1 as true and 0 as false.
CREATE TABLE testbool ( sometext VARCHAR2(10), is_checked NUMBER(1) );
You can add a check constraint in the column to ensure that no other values can be entered.
CREATE TABLE testbool ( sometext VARCHAR2(10), is_checked NUMBER(1), CONSTRAINT ck_testbool_ischk CHECK (is_checked IN (1,0)) );
This means that you can insert a 1 (for TRUE) or 0 (for FALSE) in this column:
INSERT INTO testbool (sometext, is_checked) VALUES (‘a’, 1); INSERT INTO testbool (sometext, is_checked) VALUES (‘b’, 0);
When you select these values, they are displayed as 1 or 0.
SELECT sometext, is_checked FROM testbool; SOMETEXT IS_CHECKED to 1 b 0
You can convert these values to other values for display in an application if you do not want to display 1 or 0
.
There are a few other methods of storing Booleans, which I’ve highlighted at the end of the post, including the reasons why I don’t recommend them.
PL/SQL has a Boolean data type,
so if you are writing PL/SQL code (a stored procedure, for example), you can use the Boolean data type.
SQL Server Boolean
There is no Boolean data type in SQL Server
.
However, a common option is to use the
BIT data type.
A BIT data type is used to store bit values from 1 to 64. Therefore, a BIT field can be used for Booleans, providing 1 for TRUE and 0 for FALSE.
CREATE TABLE testbool ( sometext VARCHAR(10), is_checked BIT );
This means that you can insert a 1 (for TRUE) or 0 (for FALSE) in this column. You do not need to add a check constraint because BIT values only accept 1 or 0.
INSERT INTO testbool (sometext, is_checked) VALUES (‘a’, 1); INSERT INTO testbool (sometext, is_checked) VALUES (‘b’, 0);
When you select these values, they are displayed as 1 or 0.
SELECT sometext, is_checked FROM testbool; SOMETEXT IS_CHECKED to 1 b 0
You can convert these values to other values for display in an application if you do not want to display 1 or 0
.
Boolean MySQL
MySQL has a Boolean data type.
However, it is only a synonym for TINYINT, which is a numeric
field. A
common alternative is to use a BIT field
.
A BIT data type is used to store bit values from 1 to 64. Therefore, a BIT(1) field can be used for Booleans, providing 1 for TRUE and 0 for FALSE. Just like in SQL Server.
CREATE TABLE testbool ( sometext VARCHAR(10), is_checked BIT(1) );
This means that you can insert a 1 (for TRUE) or 0 (for FALSE) in this column. You do not need to add a check constraint because BIT values only accept 1 or 0.
INSERT INTO testbool (sometext, is_checked) VALUES (‘a’, 1); INSERT INTO testbool (sometext, is_checked) VALUES (‘b’, 0);
When you select these values, they are displayed as 1 or 0.
SELECT sometext, is_checked FROM testbool; SOMETEXT IS_CHECKED to 1 b 0
You can convert these values to other values for display in an application if you do not want to display
1 or 0. Alternatively, because MySQL maps the BOOLEAN data type to the TINYINT data type,
using BOOLEAN or TINYINT(1) can get the same result
.
There are a few other methods of storing Booleans, which I’ve highlighted at the end of the post, including the reasons why I don’t recommend them
. PostgreSQL Boolean PostgreSQL has a
Boolean
data type.
You can store true or false in
this column, which can be represented by many different values
: TRUE: represented by TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘on’, ‘1’ FALSE: represented by FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘off’, ‘
- 0′
The keywords TRUE and FALSE are preferred.
An example of a Boolean data type in
PostgreSQL
is: CREATE TABLE testbool ( sometext TEXT, is_checked BOOLEAN );
You can insert a Boolean value by using the INSERT statement:
INSERT INTO testbool (sometext, is_checked) VALUES (‘a’, TRUE); INSERT INTO testbool (sometext, is_checked) VALUES (‘b’, FALSE);
When a Boolean value is selected, it is displayed as either ‘t’ or ‘f’.
SELECT sometext, is_checked FROM testbool; SOMETEXT IS_CHECKED a t b f
Boolean alternatives
If your database does not support Boolean data types, there are several alternative methods
. NUMBER(1) with 1
and 0
This is a good option for Oracle because it does not include a
BIT data type.
You can create a field with a data type NUMBER(1), and use a check constraint to apply 1 or 0.
CREATE TABLE testbool ( sometext VARCHAR2(10), is_checked NUMBER(1), CONSTRAINT ck_testbool_ischk CHECK (is_checked IN (1,0)) );
This will work like a Boolean. However, if you are using SQL Server or MySQL, the BIT data type is more appropriate because it is simpler and has built-in checking whether a value is 1 or 0.
Otherwise, it is a good alternative.
CHAR(1)
or VARCHAR(1) with T and F
Using a single-character CHAR field is another option that has been suggested. The two characters could be T for TRUE and F for FALSE.
CREATE TABLE testbool ( sometext VARCHAR2(10), is_checked CHAR(1) CONSTRAINT ck_testbool_ischk CHECK (is_checked IN (‘T’, ‘F’)) );
You can add a check constraint to make sure the value is T or F.
However, the problem is that it is not globally recognized. In English, T and F can stand for True or False. But what about French or German or Spanish or any other language?
If you expect other developers from other countries, then T/F may not be the best choice.
CHAR(1)
or VARCHAR(1) with Y and
N
The same reasoning as the use of T/F can be applied to the use of Y/N. A single-character field designed to contain only Y or N can be used for Boolean.
The verification restriction is optional but recommended.
CREATE TABLE testbool ( sometext VARCHAR2(10), is_checked CHAR(1) CONSTRAINT ck_testbool_ischk CHECK (is_checked IN (‘Y’, ‘N’)) ); However, like the use of
T/F, the use of Y/N is not recognizable in other languages. It might confuse other developers who aren’t used to seeing Y for Yes and N for No.
The
Boolean data type that is common in other programming languages is not always available in SQL. PostgreSQL has a Boolean data type, and other database providers allow other methods to capture the true/false values used for Booleans.