Open In App

PostgreSQL – Boolean Data Type

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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:


Last Updated : 08 Jun, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads