PostgreSQL Boolean Data Types

June 17, 2022 | by Arround The Web | No comments

PostgreSQL Boolean Data Types

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 apt-get -y install postgresql postgresql-contrib

$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

$ sudo -u postgres psql

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’:

# CREATE DATABASE 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.

# CREATE TABLE technicians (

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:

# INSERT INTO technicians VALUES (1, 'Zafar Ali', 'true');

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.

# INSERT INTO technicians VALUES (2, 'Kabir Hossain', 0);

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.

# INSERT INTO technicians VALUES (3, 'Abir Hasan', 'false');

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:

# INSERT INTO technicians VALUES (5, 'Rebeka Ali', 't');

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’.

# CREATE TABLE products (

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:

# INSERT INTO products VALUES (1, 'Antivirus', 'f')

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.

# INSERT INTO products VALUES (2, 'Monitor');

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:

# SELECT * FROM technicians;

Run the SELECT following select query to retrieve all records from the technicians table where the value of the available field is ‘false’:

# SELECT * FROM technicians WHERE available = '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’:

# SELECT * FROM technicians WHERE available = 't' OR available = '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:

# SELECT * FROM products;

Run the following select query to retrieve all records from the products table where the value of the physical_product field is ‘True’:

# SELECT * FROM products WHERE physical_product = '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.

Source: linuxhint.com