PostgreSQL – Boolean Data Type

In this article, we will look into the PostgreSQL Boolean data type and it’s implementation while designing a database table. PostgreSQL boolean data type has 3 states namely TRUE< FALSE and NULL. It uses single byte for storing boolean value and can be abbreviated as BOOL.
The below table depicts all valid literal values for TRUE and FALSE in PostgreSQL:

True	False
true	false
‘t’	‘f’
‘true’	‘false’
‘y’	‘n’
‘yes’	‘no’
1	0

Now, let’s look into a few examples. For the same reason let’s create a sample database(say, bookstore) using the below command:

CREATE DATABASE bookstore;

Add a table (say, book_availability) to the bookstore database using the below command:

CREATE TABLE book_availability (
    book_id INT NOT NULL PRIMARY KEY,
    available BOOLEAN NOT NULL
);

Now insert some data to the book_availability table using the below command:

INSERT INTO book_availability (book_id, available)
VALUES
    (100, TRUE),
    (200, FALSE),
    (300, 't'),
    (400, '1'),
    (500, 'y'),
    (600, 'yes'),
    (700, 'no'),
    (800, '0');

Now that our database is ready let’s look into some examples.

Example 1:
In this example we will query for all the available books in the bookstore.

SELECT
    *
FROM
    book_availability
WHERE
    available = 'yes';

Output:

Example 2:
In this example we will query for all books that are NOT available in the bookstore.

SELECT
    *
FROM
    book_availability
WHERE
    available = 'no';

Output:

Example 3:
In this example we will make the use of NOT operator to check for books that are not available in the bookstore.

SELECT
    *
FROM
    book_availability
WHERE
    NOT available;

Output: