PostgreSQL Boolean

In this section, we are going to understand the working of PostgreSQL Boolean data types, which allows us to design the database tables. And we also see examples of the Boolean data type.

What is PostgreSQL Boolean Data Type?

A Boolean is a commonly used data type in PostgreSQL, which always to return two probable values, such as TRUE and FALSE. The Boolean data type can be abbreviated as BOOL.

In other words, we can say that the PostgreSQL Boolean data type uses one byte for storing a Boolean value in the database. And it can always use to get approval in the form of YES or No value.

PostgreSQL allows us a single Boolean data type, known as BOOLEAN containing three different True, False, and Null values.

Note: The SQL supports TRUE, FALSE, and NULL values of a Boolean data type. But PostgreSQL is only flexible using the TRUE and FALSE values.

We have the following table, which contains the valid literal values for TRUE and FALSE that are supported by PostgreSQL:

Note: As we can see in the above table, all the constant values apart from True and False must enclose with in a single quote. And the leading or trailing whitespace does not matter in the above table.

Examples of PostgreSQL Boolean data type

Let us see different examples to understand how the PostgreSQL Boolean data type works.

For this, we will create one new table with the help of the CREATE command and inserting some values using the INSERT command.

We are going to create table Items_in_stock by using the CREATE command into the Javatpoint database:

Output

The Items_in_storck table has been successfully created after executing the above commands, as shown in the below screenshot:

Once the Items_in_storck table has been generated, we use several literal values for the Boolean values with the INSERT command's help.

Output

After executing the above command, we will get the following message: the value has been inserted successfully into the Items_in_stock table.

After creating and inserting the Items_in_stock table's values, we will use the below command to find those items which are in stock:

Output

After successfully implementing the above command, we will get the following result: The Items are available for the particular Items_id into the Items_in_stock table.

We can also retrieve the True values with the help of the Boolean column without using any operator.

As we can see in the below example, the following statement is used to retrieve all the items which are in the stock:

Output

On executing the above command, we will get a similar output as compared to the above command:

In the same way, if we check the false values, we can compare the value of the Boolean column in contrast to any valid Boolean constants.

The below command is used to return the Items which are not in the stock:

Output

After successfully implementing the above command, we will get the following result: The Items are not available for the particular Items_id into the Items_in_stock table.

Or we can use the NOT operator to identify if values in the Boolean column are false as shown in the following command:

Output

On executing the above command, we will get a similar output as compared to the above command:

Example of Setting a default value of the Boolean column

We are using the SET DEFAULT clause in the ALTER TABLE command to set a default value for an existing Boolean column.

In the following example, we used the below ALTER TABLE command to set the default value for the in_stock column in the Items_in_stock table:

Output

After executing the above command, we will get the below message that the Items_in_stock table has been altered successfully.

PostgreSQL uses the FALSE values if we insert a row without describing the value for the In_stock column:

Output

On implementing the above command, we will get the below message: the particular value has been inserted successfully in the Items_in_stock table.

In the following command, we will use the Select statement to retrieve values for the Items_id 901:

Output

After executing the above command, we will get the following output:

Similarly, while we are creating a table with the help of the Create command, we can also set a default value for a Boolean column.

And we will use the DEFAULT constraint in the column definition as we can see in the following command:

Output

After implementing the above command, we will get the below message: the test_ boolean table has been created successfully.

Overview

In the PostgreSQL Boolean data type section, we have learned the following topics:

• The Boolean data type is used to store the Boolean data.
• We use the PostgreSQL Boolean data type to set the default value for the Boolean column.