SQL Boolean Data Type

Do you need to store a boolean value in your SQL database? Does the data type even exist? Read this article and find out.

What is a Boolean?

A boolean is a data type that can store either a True or False value. This is often stored as 1 (true) or 0 (false). It’s 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 it depends on which database vendor you’re using.

The good news is that even if there isn’t a 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 vendor:

Database	Boolean?	Use Instead
Oracle	No	NUMBER(1)
SQL Server	No	BIT
MySQL	No	BIT or TINYINT
PostgreSQL	Yes

If you want to get an easy reference PDF for all of the major SQL vendors, get the SQL Cheat Sheets here:

 

Oracle Boolean

Is there a boolean data type in Oracle SQL?

No, there isn’t.

You can’t declare a column with the BOOLEAN data type.

However, there are several alternatives, which I’ve detailed below.

The recommended way of storing 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 on the column to ensure other values cannot be entered.

CREATE TABLE testbool (
  sometext VARCHAR2(10),
  is_checked NUMBER(1),
  CONSTRAINT ck_testbool_ischk CHECK (is_checked IN (1,0))
);

This means you can insert either a 1 (for TRUE) or 0 (for FALSE) into 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 shown just as 1 or 0.

SELECT sometext, is_checked
FROM testbool;

SOMETEXT	IS_CHECKED
a	1
b	0

You can convert these values into other values to display in an application if you don’t want to display 1 or 0.

There are a few other methods for storing booleans, which I’ve highlighted at the end of the post, including the reasons I don’t recommend them.

PL/SQL does have a boolean data type, so if you’re 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. So, 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 you can insert either a 1 (for TRUE) or 0 (for FALSE) into this column. There is no 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 shown as 1 or 0.

SELECT sometext, is_checked
FROM testbool;

SOMETEXT	IS_CHECKED
a	1
b	0

You can convert these values into other values to display in an application if you don’t want to display 1 or 0.

 

MySQL Boolean

MySQL does have a boolean data type. However, it is just 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. So, 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 you can insert either a 1 (for TRUE) or 0 (for FALSE) into this column. There is no 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 shown just as 1 or 0.

SELECT sometext, is_checked
FROM testbool;

SOMETEXT	IS_CHECKED
a	1
b	0

You can convert these values into other values to display in an application if you don’t want to display 1 or 0.

Alternatively, because MySQL maps the BOOLEAN data type to the TINYINT data type, using either BOOLEAN or TINYINT(1) can get the same result.

There are a few other methods for storing booleans, which I’ve highlighted at the end of the post, including the reasons I don’t recommend them.

 

PostgreSQL Boolean

PostgreSQL does have a boolean data type.

You can store either 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 using the INSERT statement:

INSERT INTO testbool (sometext, is_checked) VALUES ('a', TRUE);
INSERT INTO testbool (sometext, is_checked) VALUES ('b', FALSE);

When you select a boolean value, 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 as it does not include a BIT data type.

You can create a field with a NUMBER(1) data type, and use a check constraint to enforce either 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 as a boolean. However, if you’re using SQL Server or MySQL, the BIT data type is more appropriate as it is simpler and has inbuilt checking if a value is 1 or 0.

Otherwise, it’s 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 could add a check constraint to ensure the value is either T or F.

However, the problem is that it’s not globally recognised. 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’re expecting 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 using T/F can be applied to using Y/N. A single-character field that is designed to hold only Y or N can be used for boolean.

The check constraint 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, just like using T/F, using Y/N is not recognisable in other languages. It could confuse other developers who aren’t used to seeing Y for Yes and N for No.

 

Conclusion

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 vendors allow for other methods for capturing the true/false values used for booleans.

If you want to get an easy reference PDF for all of the major SQL vendors, get the SQL Cheat Sheets here: