PostgreSQL – BOOLEAN Data Type With Examples

In this tutorial, we will learn about the BOOLEAN type in PostgreSQL. Unlike the BOOLEAN type in MySQL, which is just a synonym for the TINYINT(1), PostgreSQL provides the BOOLEAN type as per the SQL standards. Moreover, it offers you more options for the BOOLEAN type, which is interesting to learn. So without wasting time, let’s get started!

Introduction to the BOOLEAN Type

In computer programming, a boolean simply denotes the value as a ‘true’ or ‘false’. This can be also represented in the form of binary bits, i.e, 0 and 1.

Almost all statically typed programming languages provide the boolean type. Note that, all of them can recognize the boolean value in string form (true/false) as valid boolean values.

In PostgreSQL, the boolean values can be denoted using even more formats. Let’s see what are those.

BOOLEAN in PostgreSQL

In PostgreSQL, the boolean type of field can have the following values: true, false and unknown, also known as null.

Note that, the storage size of the boolean type is one byte.

Boolean type of column accepts the following string representations for the ‘true’ condition: true, yes, on, and ‘1’

On the other hand, a ‘false’ state can be represented by: false, no, off, ‘and 0’.

Apart from the above forms, unique prefixes of these representations such as ‘t’, ‘f’, ‘y’, and ‘n’ are also accepted.

Note that, all the leading or trailing spaces are automatically ignored along with the case.

If you notice, representing the boolean state using the ‘yes/no’ and ‘on/off’ is a different feature that PostgreSQL provides which is missing in many programming languages and DBMSes.

Now let’s see the boolean type in action using the examples.

PostgreSQL BOOLEAN Type Examples

Let’s log in to our PostgreSQL psql shell and select the database.

psql -U postgres
\l
\c journaldevCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now we will create a table to store the car availability.

CREATE TABLE cars(
  id serial PRIMARY KEY,
  name VARCHAR(100),
  isAvailable BOOLEAN
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the first column will store the sequential id which is auto-incremented. The second column will store the car names, whereas, the third column will store the boolean value for car availability.

Now let’s insert values into the table with different formats of the boolean state.

INSERT INTO cars(name,isAvailable)
VALUES('Brezza', true);

INSERT INTO cars(name,isAvailable)
VALUES('Thar', '1');

INSERT INTO cars(name,isAvailable)
VALUES('Creta', 'no');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here we have used three different formats of the boolean values. Note that, the ‘1’ and ‘0’ must be in the form of a string, otherwise it will show you an error that the column “isavailable” is of type boolean but the expression is of type integer.

Let’s see the output now.

SELECT * FROM cars;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, even if we specify any format of a boolean value, it gets stored as ‘t’ and ‘f’.

Now let’s apply the WHERE clause and try to retrieve the data.

SELECT * FROM cars
WHERE isAvailable = 'off';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we are using the WHERE condition to get all the records that have the value ‘off’ of the column isAvailable.

As you can see, even if you specify any format of the boolean value with the where clause, it gives you the correct result.

As mentioned, the boolean type also accepts the null value. Let’s try using it.

INSERT INTO cars(name, isAvailable) 
VALUES('Amaze', NULL);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Let’s try retrieving the data for the NULL values using the where condition.

SELECT * FROM cars WHERE isAvailable is  NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, we get the correct result. However, this will not work all the time.

The PostgreSQL parser can detect the values ‘true’ and ‘false’ are of type boolean. However, this is not so for the null. You might need to convert the NULL value to the BOOLEAN type explicitly using the syntax NULL::BOOLEAN.

Conclusion

In this tutorial, we have learned about the BOOLEAN type. This is one of the easiest data types that you can learn in any programming language. As we saw, there are multiple formats you can represent the boolean value in. However, we highly prefer to just stick to any particular format as it will be easier to understand throughout the development process. I hope you learned some interesting things in this tutorial, if you did, share it with your friends!

Reference

PostgreSQL official documentation.