PostgreSQL: Boolean Data Type

PostgreSQL supports BOOLEAN data types, that can have values as TRUE, FALSE, or NULL. Postgres takes one byte to store BOOLEAN values.

As per Standard SQL, Boolean values are TRUE, FALSE, or NULL, but PostgreSQL is flexible and allows other values can be stored in BOOLEAN data type. PostgreSQL then internally converts such values to True or False.

Note that above, except true and false, all other literal values must be enclosed in single quotes ' '.

Let's create a Product table with one column as BOOLEAN datatype and insert some data into it. A short keyword BOOL can also be used to create a Boolean data type column.

CREATE TABLE IF NOT EXISTS Product
(prod_id 	INT PRIMARY KEY,
name VARCHAR(50),
is_available BOOLEAN);

INSERT INTO PRODUCT(PROD_ID, NAME, IS_AVAILABLE)
VALUES(1,'Keyboard',TRUE);

INSERT INTO PRODUCT(PROD_ID, NAME, IS_AVAILABLE)
VALUES(2,'Mouse',FALSE);

INSERT INTO PRODUCT(PROD_ID, NAME, IS_AVAILABLE)
VALUES(3,'Laptop','t');

INSERT INTO PRODUCT(PROD_ID, NAME, IS_AVAILABLE)
VALUES(4,'Monitor','yes');

INSERT INTO PRODUCT(PROD_ID, NAME, IS_AVAILABLE)
VALUES(5,'USB','1');

INSERT INTO PRODUCT(PROD_ID, NAME, IS_AVAILABLE)
VALUES(6,'IPAD','y');

INSERT INTO PRODUCT(PROD_ID, NAME, IS_AVAILABLE)
VALUES(7,'Printer','no');

INSERT INTO PRODUCT(PROD_ID, NAME, IS_AVAILABLE)
VALUES(8,'Scanner','0');

Now let's fetch table data.

Note that we inserted data using different values for the BOOLEAN datatype, but internally it stored data in the IS_AVAILABLE field as true/false only.

In the same way, we can query data from a table on Boolean column using any of the values. Here we will select only the products which are available with IS_AVAILABLE flag as false.

SELECT * FROM Product WHERE is_available = '0';

You can query the true values just by using the column in the where clause without any operator.

SELECT * FROM Product WHERE is_available;

Set DEFAULT value to BOOLEAN column:

You can set the DEFAULT value to the BOOLEAN column while creating a table by specifying it in CREATE TABLE statement or for an existing BOOLEAN column by giving ALTER TABLE statement.

Let's set default value as true for Product table's IS_AVAILABLE column.

ALTER TABLE PRODUCT
ALTER COLUMN IS_AVAILABLE
SET DEFAULT TRUE;

Now when you insert data into the Product table without specifying data for IS_AVAILABLE Column, by default it will set it to true.

INSERT INTO PRODUCT(PROD_ID, NAME)
VALUES(9,'Cable');

Let's check data in Product table

As you can see above, data for prod_id = 9 and the name = 'cable' is added with IS_AVAILABLE flag as true.