In PostgreSQL, the Boolean data type is used to store true or false values. It is a simple data type that can be used in various logical operations and conditions.

Syntax

The syntax for declaring a Boolean column in a table is straightforward. You simply specify the column name followed by the keyword BOOLEAN.


CREATE TABLE example_table (
    is_active BOOLEAN
);

Default Values

You can set a default value for a Boolean column. This value will be used if no explicit value is provided during an insert operation.


CREATE TABLE example_table (
    is_active BOOLEAN DEFAULT TRUE
);

Inserting Boolean Values

When inserting data into a Boolean column, you can use the keywords TRUE, FALSE, or their aliases 't', 'f'.


INSERT INTO example_table (is_active) VALUES (TRUE);
INSERT INTO example_table (is_active) VALUES ('f');

Selecting Boolean Values

You can retrieve Boolean values using standard SQL SELECT queries. The results will display as either true or false.


SELECT is_active FROM example_table;

Using Boolean in Conditions

Boolean values are often used in WHERE clauses to filter records based on true or false conditions.


SELECT * FROM example_table WHERE is_active = TRUE;

Updating Boolean Values

You can update Boolean values using the UPDATE statement, changing them from true to false or vice versa.


UPDATE example_table SET is_active = FALSE WHERE id = 1;

Boolean Expressions

Boolean expressions can be used to perform logical operations such as AND, OR, and NOT.


SELECT * FROM example_table WHERE is_active = TRUE AND another_condition = FALSE;

Checking for NULL

A Boolean field can also be NULL. You can check for NULL values using the IS NULL condition.


SELECT * FROM example_table WHERE is_active IS NULL;

Casting to Boolean

You can cast other data types to Boolean using the CAST operator or the :: syntax.


SELECT CAST('true' AS BOOLEAN);
SELECT 'false'::BOOLEAN;