PostgreSQL is an open-source object-oriented based database system. It is a powerful database system that supports both relational and non-relational data types. The Boolean data type is a commonly used data type that can accept three types of values: True, False, and NULL. The short form of this data type is bool and one byte is used to store the Boolean data. The True value of the Boolean data can also be denoted by ‘yes’, ‘y’, ‘true’, and 1. The False value of the Boolean data can also be denoted by ‘no’, ‘n’, ‘false’, and 0.
Pre-requisites:
You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:
$ sudo systemctl start postgresql.service
Run the following command to login to PostgreSQL with root permission:
Use of Boolean data type in PostgreSQL tables:
Before creating any table with the Boolean data type, you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:
The following output will appear after creating the database:
Example-1: Create a table using the Boolean data type
Create a table named ‘technicians’ in the current database with three fields. The first field name is tech_id, the data type is an integer and it is the primary key of the table. The second field name is name and the data type is character. The third field name is available and the data type is Boolean.
tech_id INT NOT NULL PRIMARY KEY,
name CHARACTER(10) NOT NULL,
available BOOLEAN NOT NULL
);
The following output will appear if the table is created successfully:
Run the following command to INSERT a record into the technicians table where ‘true’ is used for the Boolean value:
The following output will appear after executing the above insert query:
Run the following INSERT command to insert a record into the technicians table where 0 is used for the Boolean value. The 0 is not acceptable for the Boolean value in PostgreSQL. So, an error message will appear.
The following output will appear after executing the above insert query. The output shows an error message that indicates that the type of the 0 is Integer, not Boolean.
Run the following INSERT command to insert a record into the technicians table where ‘false’ is used for the Boolean value.
The following output will appear after executing the above insert query:
Run the following INSERT command to insert a record into the technicians table where ‘t’ is used for the Boolean value:
The following output will appear after executing the above insert query:
Example-2: Create a table using Bool data type
Create a table named ‘products’ in the current database with three fields. The first field name is id, the data type is an integer and it is the primary key of the table. The second field name is name and the data type is character. The third field name is physical_product, the data type is BOOL, and the default value of the field is ‘true’.
id INT NOT NULL PRIMARY KEY,
name CHARACTER(10) NOT NULL,
physical_product BOOL NOT NULL DEFAULT 'true'
);
The following output will appear if the table is created successfully:
Run the following command to insert a record into the products table where ‘f’ is used for the BOOL value:
Run the following INSERT command to insert a record into the products table where no value is provided for the BOOL data. Here, the default value of the field will be inserted.
The following output will appear after executing the above two insert queries:
Check the content of the tables:
Run the SELECT following select query to retrieve all records from the technicians table:
Run the SELECT following select query to retrieve all records from the technicians table where the value of the available field is ‘false’:
Run the SELECT following select query to retrieve all records from the technicians table where the value of the available field is ‘t’ or ‘true’:
The following output will appear after executing the above three ‘select’ queries. The output of the first query shows all records of the table. The output of the second query shows those records of the table where the value of the available field is ‘f’. The output of the third query shows those records of the table where the value of the available field is ‘t’.
Run the following select query to retrieve all records from the products table:
Run the following select query to retrieve all records from the products table where the value of the physical_product field is ‘True’:
The following output will appear after executing the above two ‘select’ queries. The output of the first query shows all records of the table. The output of the second query shows those records of the table where the value of the available field is ‘t’.
Conclusion:
Different uses of Boolean or BOOL data types in PostgreSQL tables have been shown in this tutorial by using multiple examples to clarify the purpose of using Boolean data types in the table of the PostgreSQL database.
