Introduction to the PostgreSQL Boolean type

PostgreSQL supports a single Booleandata type:BOOLEAN that can have three values:true,false andNULL.

PostgreSQL uses one byte for storing a boolean value in the database. TheBOOLEAN can be abbreviated asBOOL.

In standard SQL, a Boolean value can beTRUE,FALSE, orNULL. However, PostgreSQL is quite flexible when dealing withTRUE andFALSE values.

The following table shows the valid literal values forTRUE andFALSE in PostgreSQL.

Note that the leading or trailing whitespace does not matter and all the constant values except fortrue andfalse 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 calledstock_availability to log which products are available.

CREATE TABLE stock_availability (   product_idINT PRIMARY KEY,   availableBOOLEAN NOT NULL);

Second,insert some sample data into the stock_availability table. We use various literal values for the boolean values.

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, check for the availability of products:

SELECT *FROM stock_availabilityWHERE available= 'yes';

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. For example, the following query returns all available products:

SELECT *FROM stock_availabilityWHERE available;

Similarly, if you want to look forfalse values, you compare the value of the Boolean column against any valid Boolean constants.

The following query returns the products that are not available.

SELECT  *FROM  stock_availabilityWHERE  available= 'no';

product_id | available------------+-----------        200 | f        700 | f        800 | f(3 rows)

Alternatively, you can use theNOT operator to check if values in the Boolean column are false like this:

SELECT  *FROM  stock_availabilityWHERE  NOT available;

Set the default values for Boolean columns

To set a default value for an existing Boolean column, you use theSET DEFAULT clause in theALTER TABLE statement.

For example, the followingALTER TABLE statement sets the default value for theavailable column in thestock_availability table:

ALTER TABLE stock_availabilityALTER COLUMN availableSET DEFAULT FALSE;

If you insert a row without specifying the value for theavailable column, PostgreSQL will useFALSE by default:

INSERT INTO stock_availability (product_id)VALUES (900);

SELECT *FROM stock_availabilityWHERE product_id= 900;

product_id | available------------+-----------        900 | f(1 row)

Likewise, if you want to set a default value for a Boolean column when youcreate a table, you use theDEFAULT constraint in the column definition as follows:

CREATE TABLE boolean_demo (   ...   is_ok BOOLDEFAULT 't');

Summary

• Use the PostgreSQLBOOLEAN datatype to store the boolean data.

Last updated onFebruary 1, 2024

On this page

• Introduction to the PostgreSQL Boolean type
• PostgreSQL Boolean examples
• Set the default values for Boolean columns
• Summary