PostgreSQL offers a BOOLEAN data type with three states: TRUE, FALSE, or NULL. It requires only 1 byte to store a value in a database, and it returns one of two probable values: True or False. In Postgres, the BOOLEAN data type is abbreviated as BOOL.

The BOOLEAN data type is used when you have to get some sort of approval, like YES or NO. Some popular use cases of BOOLEAN data type include “checking the availability of something”, “age restriction approval”, and so on.

This write-up will teach you how to use the BOOLEAN data type using some examples. So, let’s begin.

How Does the BOOLEAN Data Type Work in PostgreSQL?

In PostgreSQL, there are some valid literal values for BOOLEAN true and false. These values must be enclosed in single quotations. However, the constant values True and False work fine with or without single quotes.

- The valid literal values for the BOOLEAN true include true, ‘t’, ‘true’, ‘y’, ‘yes’, and ‘1’.

- The valid literal values for the BOOLEAN false include false, ‘f’, ‘false’, ‘n’, ‘no’, and ‘0’.

Let’s consider different examples to understand the working of BOOLEAN data type in a better way.

Example #1: How to Create a Column With BOOLEAN Data Type?

Let’s create a table named book_details that includes three columns: book_id, book_name, and is_available. We will set the data type of book_id as INT, book_name as TEXT, and is_available as BOOLEAN:

CREATE TABLE book_details ( 
book_id INT PRIMARY KEY, 
book_name TEXT, 
is_available BOOLEAN NOT NULL 
);

The table named book_details has been created successfully. Let’s verify the existence of the book_details table using the below command:

SELECT * FROM book_details;

From the output, you can observe that the table columns with their respective data types have been created successfully.

Example #2: How to Insert BOOLEAN Values Using Different Literal Values to a Table?

Let’s insert the data into the newly created table named book_details. We will utilize various literal values to insert the boolean values into a table:

INSERT INTO book_details (book_id, book_name, is_available) 
VALUES 
(1, 'The Great Gatsby', TRUE), 
(2, 'The Picture of Dorian Gray', FALSE),
(3, 'Great Expectations', 't'), 
(4, 'Wuthering Heights', 'f'), 
(5, 'The Kite Runner', '1'), 
(6, 'The Catcher in the Rye', '0'),
(7, 'The Lord of the Rings', 'y'), 
(8, 'His Dark Materials', 'n'), 
(9, 'To Kill a Mockingbird', 'yes'), 
(10, 'The Grapes of Wrath', 'no'),
(11, 'Frankenstein', 'TRUE'), 
(12, 'Think and Grow Rich', 'FALSE');

Twelve rows have been inserted into the book_details table successfully. Let’s execute the SELECT query to verify data insertion:

SELECT * FROM book_details;

All the records have been inserted into the book_details table successfully.

Example #3: How to Fetch All the Records That are True?

Let’s fetch only those books that are available in stock. We can use one of the following valid literal values true, ‘t’, ‘true’, ‘y’, ‘yes’, or ‘1’:

SELECT * FROM book_details
WHERE is_available = true;

The SELECT query succeeded in fetching all those books that are available in stock. We utilized the constant value “true” to fetch the available books. However, you can use any other valid literal like ‘t’, ‘1’, ‘y’, etc, to fetch the books that are in stock.

Example #4: How to Fetch All the Records That are False?

We can use one of the following valid literal values: false, ‘f’, ‘false’, ‘n’, ‘no’, or ‘0’. This example will fetch only those books that are unavailable’:

SELECT * FROM book_details
WHERE is_available = '0';

The SELECT query fetched all those books that are not in stock.

Example #5: How to Set The Default Value of a Boolean Column?

Use the SET DEFAULT clause with the aid of ALTER TABLE and ALTER COLUMN commands to set the default value of an existing boolean column. In this example, we will set the default value of the is_available column as FALSE:

ALTER TABLE book_details
ALTER COLUMN is_available
SET DEFAULT FALSE;

The output verified that the default value of the is_available column had been set as False successfully. Let’s insert 13 into the book_id column and ‘Lord of the Flies’ into the book_name column:

INSERT INTO book_details (book_id, book_name)
VALUES (13, 'Lord of the Flies');

We didn’t insert anything in the is_available column against the book_id 13:

One row has been added to the book_details table. Let’s execute the SELECT query to show the details of the selected table:

SELECT * FROM book_details;

At the time of data insertion against book_id 13, we didn’t specify the value for the is_available column. However, we can see in the output that Postgres inserted false in the is_available column for book_id = 13.

Conclusion

In PostgreSQL, the BOOLEAN or BOOL data type takes only 1 byte to store a value in a database, and it returns one of two probable values: True or False. In PostgreSQL, there are some valid literal values for BOOLEAN true and false. For example, true, ‘t’, ‘true’, ‘y’, ‘yes’, and ‘1’ are valid literal values for the BOOLEAN true. While the valid literal values for the BOOLEAN false include false, ‘f’, ‘false’, ‘n’, ‘no’, and ‘0’. This post provided detailed knowledge about the BOOLEAN data type with the help of examples.