PostgreSQL

Postgres Boolean Type

Boolean Type Value

PostgreSQL uses the standard values of BOOLEAN. It uses a single byte to store a Boolean value in the Postgres database. Boolean has the short word ‘bool’. This article will cover the Boolean column creation, insertion, and fetching of the data.

Working of Boolean Data Type in SQL

This boolean data type is a simple PostgreSQL data type that represents the structure of true and false only. If we define the value of Boolean as false, then all the false values will be fetched and similar is the case with the true value. The true value of the Boolean data type is: true, t, yes, y, 1 and for the false value it is: f, no, n, 0. All these values should be enclosed in quotes other than true and false themselves.

SYNTAX

There is no specified syntax for the Boolean value. As it is a data type used as a column value, we can use it in the CREATE statement at the time of table creation.

CREATE TABLE name_of_table (Column_name INTEGER, Column_name BOOLEAN NOT NULL );

Similarly in the ALTER command and the insert command.

Alter table name_of_table ADD COLUMN column_name BOOLEAN;
Insert into name_of_table values (true, 't', 'true', 'y', 'yes', '1');

Implementation

We will create a table ‘toy’ that will store the information regarding toys having the attributes id, name of the toy, and the third column name ‘availability’ as a Boolean type. Use a CREATE Postgres command.

>> Create table toy (id INT PRIMARY KEY, name text, Availability BOOLEAN NOT NULL);

As you enter the command, after the procession, a message will be declared that will show that the table is created successfully. After the table creation, now it’s time to enter data in the relation. So, we will use an INSERT command. We have inserted sample values randomly. The syntax of the insert statement contains the name of the table, its column names, and then the values that are to be inserted. Each row is written separately in parenthesis.

>> INSERT INTO toy (id, name, Availability)

VALUES

(7, 'Car', TRUE),

(38, 'doll', FALSE),

(17, 'bike', 't'),

(3, 'balloons', 'FALSE'),

(9, 'bear', 'y'),

(46, 'Drum' , 'yes'),

(4, 'duck', 'no'),

(15, 'Ball', '0'),

(11, 'Bat', '1');

The column ‘Availability’ status column is a Boolean type and we have written each type of true and false values. Either it is true t or 1 for true. And 0, false, or f for false, each value will be dealt as “f” for false, when it is entered in the Postgres table. The table will contain a uniform representation for the Boolean values. After the entry of data, you can see the record by using a SELECT command.

>> select * from toy;

Example 1

The first example deals with the selection of all the toys that have availability status true. This is done by using a WHERE clause in the select statement. This ‘where’ clause will help us to identify the current condition. We have used the ‘yes’ value for the true ones. An asterisk sign is used to fetch all the records regarding the command.

>> select * from toy where availability = 'yes';

You can see that all the resultant values that are present in the table are those that have the availability of ‘true’ value. Boolean values are recognized in PostgreSQL by using any input value other than those that are stored in the table of the database. For instance, in this example, we have used ‘yes’ in the command, but as a result, we found that ‘t’ is fetched in the consultant table.

Example 2

Unlike the first example, this is contrary to the ‘true’ Boolean that we have used. We will fetch all the records from the table having the availability status as ‘false’.

>> select * from toy where availability = 'FALSE';

This will bring all those values with all the records having an availability column with false values.

Example 3

Following the same concept in this example, we will fetch the record that will have availability status false by using a NOT operator. ‘NOT’ is a GATE operator that is used as negation of values which means that it will bring all the negative values.

>> select * from toy where NOT availability.

So, we have seen that while using a Boolean value. The negative values can be obtained by using a NOT operator in the command.

Example 4

Till now in the example, we have seen that all the Boolean values are fetched by using a WHERE clause in the command to specify the type of Boolean value at run time in the command. But now we will see that instead of giving a run time Boolean value to be fetched. We can use a DEFAULT command to set the Boolean value by default at the start so that all the coming values get the same Boolean value that is set in the command. We will do this by using an ALTER command.

>> ALTER TABLE toy ALTER column availability SET DEFAULT FALSE;

In this command, we have set the Boolean value as ‘FALSE’ by default. Now we will enter a new row to see the update we have done.

>> INSERT INTO toy (id, name0 VALUES (90, 'Blocks');

Here, we do not have entered a Boolean value in the command. Because it will be filled automatically by the command used above. Now we will see the table records to identify the changes.

>> select * from toy;

Boolean Values Implementation via pgAdmin.

Some examples are implemented on the PostgreSQL shell. But now, we will implement the rest of the examples in the dashboard of PostgreSQL.

Here, we will add another column in the table toy.

>> Alter table toy ADD column delivery_status BOOLEAN;

Insert values in the table in each column by using the INSERT command.

>> Insert into toy values (10, 'snooker', 'yes' , 'true');

Now, we will display data by applying a condition using the WHERE clause and an AND operator to join two conditions. We will fetch those rows that have both the Boolean values true. Probably the last row we have entered.

>> SELECT * from toy where availability = true AND delivery_status = true;

You can see that the last row we have entered is fetched. Now, consider another example. For this, add two new rows having different Boolean attributes.

>> Insert into toy values (32, 'watch', '1', 'true'), (12, 'fruits', 'yes', '0');

Use the select statement to see the record. You can see that we have used mixed values of BOOLEAN to insert data and it is stored in the same way.

We now have applied a condition in which both the BOOLEAN values are different like using 1 and 0 Boolean values.

Conclusion

The article ‘ Postgres Boolean type’ contains the possible Boolean type examples. Each Boolean type either true or false is used in different ways in the relations of the Postgres database. Boolean stores a single value at a time just like the Boolean flags used in other programming languages like C++. We have implemented this article via pgAdmin and psql shell as well.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.