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
The below table depicts all valid literal values for TRUE and FALSE in PostgreSQL:
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.
In this example we will query for all the available books in the bookstore.
SELECT * FROM book_availability WHERE available = 'yes';
In this example we will query for all books that are NOT available in the bookstore.
SELECT * FROM book_availability WHERE available = 'no';
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;
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.