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:
Contact Us