Summary: in this tutorial, you will learn about the PostgreSQL Boolean data type and how to use it in designing the database tables.
Introduction to the PostgreSQL Boolean type
PostgreSQL supports a single Boolean data type: BOOLEAN that can have three states: TRUE, FALSE, and NULL. PostgreSQL uses one byte for storing a boolean value in the database. The BOOLEAN can be abbreviated as BOOL.
In standard SQL, a Boolean value can be TRUE, FALSE, or NULL. However, PostgreSQL is quite flexible when dealing with TRUE and FALSE values. The following table shows the valid literal values for TRUE and FALSE in PostgreSQL.
| True | False |
|---|---|
| true | false |
| ‘t’ | ‘f ‘ |
| ‘true’ | ‘false’ |
| ‘y’ | ‘n’ |
| ‘yes’ | ‘no’ |
| ‘1’ | ‘0’ |
Note that the leading or trailing whitespace does not matter and all the constant values except for true and false must be enclosed in single quotes.
PostgreSQL Boolean examples
Let’s take a look at some examples of using the PostgreSQL Boolean data type.
First, create a new table stock_availability to log which product are available.
1 2 3 4 | CREATE TABLE stock_availability ( product_id INT NOT NULL PRIMARY KEY, available BOOLEAN NOT NULL ); |
Second, insert some sample data into the stock_availability table. We use various literal value for the boolean values.
1 2 3 4 5 6 7 8 9 10 | INSERT INTO stock_availability (product_id, available) VALUES (100, TRUE), (200, FALSE), (300, 't'), (400, '1'), (500, 'y'), (600, 'yes'), (700, 'no'), (800, '0'); |
Third, to check the products that are available, you use the following statement:
1 2 3 4 5 6 | SELECT * FROM stock_availability WHERE available = 'yes'; |
1 2 3 4 5 6 7 8 | product_id | available ------------+----------- 100 | t 300 | t 400 | t 500 | t 600 | t (5 rows) |
You can imply the true value by using the Boolean column without any operator. The following query returns all products that are available:
1 2 3 4 5 6 | SELECT * FROM stock_availability WHERE available; |
Similarly, if you want to look for false values, you compare the value of the Boolean column against any valid Boolean constants. The following query returns the products that are not available.
1 2 3 4 5 6 | SELECT * FROM stock_availability WHERE available = 'no'; |
1 2 3 4 5 6 | product_id | available ------------+----------- 200 | f 700 | f 800 | f (3 rows) |
Or you can use the NOT operator to check if values in the Boolean column are false as the following example:
1 2 3 4 5 6 | SELECT * FROM stock_availability WHERE NOT available; |
Set a default value of the Boolean column
To set a default value for an existing Boolean column, you use the SET EFAULT clause in the ALTER TABLE statement.
For example, the following ALTER TABLE statement set the default value for the available column in the stock_availability table:
1 2 | ALTER TABLE stock_availability ALTER COLUMN available SET DEFAULT FALSE; |
If you insert a row without specifying the value for the available column, PostgreSQL uses the FALSE value by default.
1 2 3 | INSERT INTO stock_availability (product_id) VALUES (900); |
1 2 3 4 5 6 | SELECT * FROM stock_availability WHERE product_id = 900; |
1 2 3 4 | product_id | available ------------+----------- 900 | f (1 row) |
Similarly, if you want to set a default value for a Boolean column when you create a table, you use the DEFAULT clause in the column definition as follows:
1 2 3 4 | CREATE TABLE boolean_demo( ... is_ok BOOL DEFAULT 't' ); |
In this tutorial, you have learned about the PostgreSQL BOOLEAN data type and how to use it in the database table design.