PostgreSQL: The Boolean Data Type

Learn about PostgreSQL's BOOLEAN data type for storing true/false values and how to use it effectively in your database queries.

PostgreSQL Boolean Data Type

What is the PostgreSQL Boolean Data Type?

The PostgreSQL BOOLEAN data type stores true/false values. It's also known as BOOL and uses one byte of storage. It can represent a yes/no or on/off state.

PostgreSQL's BOOLEAN type accepts three possible values: TRUE, FALSE, and NULL (unknown). However, PostgreSQL primarily uses TRUE and FALSE in practice; NULL is handled separately.

Valid Literal Values

PostgreSQL accepts various representations for TRUE and FALSE:

Note: String literals (like 't', 'true', etc.) must be enclosed in single quotes.

Examples

Let's create a table and insert data using various Boolean representations:

CREATE TABLE Items_in_stock (
    Items_id INT PRIMARY KEY,
    In_stock BOOLEAN NOT NULL
);

INSERT INTO Items_in_stock (Items_id, In_stock) VALUES
(101, TRUE),
(201, FALSE),
(301, 't'),
(401, '1'),
(501, 'y'),
(601, 'yes'),
(701, 'no'),
(801, '0');

SELECT * FROM Items_in_stock WHERE In_stock = 'yes';
SELECT * FROM Items_in_stock WHERE In_stock;  --Equivalent to the above
SELECT * FROM Items_in_stock WHERE In_stock = 'no';
SELECT * FROM Items_in_stock WHERE NOT In_stock; -- Equivalent to the above

            

Setting a Default Value

You can set a default value for a Boolean column using the ALTER TABLE command:

ALTER TABLE Items_in_stock
ALTER COLUMN In_stock
SET DEFAULT FALSE;

INSERT INTO Items_in_stock (Items_id) VALUES (901);
SELECT * FROM Items_in_stock WHERE Items_id = 901;
            

Or you can set a default value during table creation:

CREATE TABLE test_boolean (
    test BOOL DEFAULT 't'
);
            

Overview

This section explained the PostgreSQL BOOLEAN data type, its valid values, and how to set default values for Boolean columns.