An In-Depth Guide to PostgreSQL Boolean Data Types

PostgreSQL is a powerful open-source relational database that includes robust support for boolean data types. Boolean columns allow storing one of three logical values: True, False, or NULL. By modeling binary states in a standardized way, developers and data engineers can build more accurate PostgreSQL-powered applications.

In this comprehensive guide, we will cover the following topics:

• Boolean Data Type Overview
• PostgreSQL Boolean Syntax and Storage
• Declaring and Setting Boolean Columns
• Inserting and Querying Boolean Values
• Boolean Operators and Performance
• Advanced Use Cases
• Industry Usage Statistics
• Summary

Boolean Data Type Overview

A boolean data type can store a logical value of either True or False. PostgreSQL uses the BOOL or BOOLEAN keywords to define a column for storing boolean values.

The main advantages of leveraging native boolean data types include:

• Space optimization: PostgreSQL uses 1 byte to store a boolean value compared to larger storage for numeric/text representations.
• Data integrity: Enforcing the boolean type ensures only valid logical values.
• Query simplicity: Boolean operators like AND/OR allow complex conditions.

Common use cases for boolean data include:

• Application flags and toggles
• User account status columns
• Email / notification opt-in tracking
• Event / status logging

By natively supporting boolean values, PostgreSQL removes the need for excessive handling in application code.

PostgreSQL Boolean Syntax and Storage

The SQL standard syntax for defining a boolean column in PostgreSQL is:

column_name BOOLEAN

The BOOL and BOOLEAN keywords function identically.

To allow NULL values, the column definition is:

column_name BOOLEAN NULL

You can also set a default boolean value when declaring the column:

registered BOOLEAN DEFAULT False

Behind the scenes, PostgreSQL stores boolean values in 1 byte, the minimum possible storage. This is significantly smaller than alternative types:

Data Type	Storage Size
BOOLEAN	1 byte
SMALLINT	2 bytes
INTEGER	4 bytes
TEXT	Variable byte length

The optimized 1 byte boolean storage helps minimize disk usage, especially for columns with a high number of rows.

Declaring and Setting Boolean Columns

Let‘s explore some examples of declaring boolean columns in PostgreSQL tables using the BOOL data type:

1. Require Non-Null Boolean

This schema creates a verified flag that cannot be null:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),  
  verified BOOLEAN NOT NULL
);

By setting NOT NULL, each user record must contain either True or False for the boolean status.

2. Set Default Boolean Value

For boolean flags, define a default value so new records don‘t require explicit values:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50), 
  email_allowed BOOLEAN DEFAULT True   
);

New user records will assume email_allowed = True automatically.

3. Add Boolean Column in ALTER Statement

To add a new boolean over time, use PostgreSQL ALTER:

ALTER TABLE users 
ADD COLUMN two_factor_enabled BOOLEAN;

-- Later, set not null 
ALTER TABLE USERS
ALTER COLUMN two_factor_enabled SET NOT NULL; 

This gradual column addition enables smoothly evolving schemas.

Inserting and Querying Boolean Values

Boolean columns accept a variety of textual formats when inserting data:

Inserting True Values

• ‘True‘
• ‘t‘
• ‘yes‘
• ‘y‘
• ‘1‘

Example inserting some True variants:

INSERT INTO users (name, verified)
VALUES 
  (‘Jean‘, ‘True‘),
  (‘Kevin‘, ‘t‘),
  (‘Linda‘, ‘y‘); 

Inserting False Values

• ‘False‘
• ‘f‘
• ‘no‘
• ‘n‘
• ‘0‘

Example of False inserts:

INSERT INTO users (name, verified)
VALUES
  (‘Mary‘, ‘f‘),
  (‘Mark‘, ‘0‘);

Any value outside these representations will raise a data error.

Querying Boolean Columns

We can query boolean columns similarly to other data types:

-- Check for True
SELECT *
FROM users
WHERE verified = True; 

-- Check for False
SELECT *
FROM users
WHERE email_allowed = False;

Other helpful querying techniques:

• IS NULL / IS NOT NULL to find NULL values
• NOT to invert boolean checks
• Indexes on boolean columns to optimize performance

Boolean Operators and Performance

One key benefit of leveraging boolean data types is enabling complex logic through Boolean operators like:

• AND – True if both values are True
• OR – True if either value is True
• NOT – Inverts a value from True to False

For example, find only verified users with names starting with ‘M‘:

SELECT *
FROM users
WHERE 
  verified = True 
  AND name LIKE ‘M%‘; 

The PostgreSQL query planner is optimized specifically around performance with boolean operators and indexed boolean columns. Testing reveals boolean operator queries can outperform equivalent CASE statement approaches.

Storing boolean flags also avoids expensive type casting operations or function calls typically needed for numeric/text storage formats. By handling the boolean logic natively, PostgreSQL provides quick access to boolean test conditions.

Advanced Use Cases

Beyond core flagging and status tracking, there are many helpful patterns for modeling boolean data:

Audit Logs

Use a boolean column to quickly track changes to records over time:

CREATE TABLE order_updates (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id),  
  changed_address BOOLEAN 
);


INSERT INTO order_updates 
  (order_id, changed_address)
VALUES
  (1, True); 

This allows easy querying to audit address changes per order, for example.

Bit Flags Column

Store up to 8 independent boolean flags in a single integer column using bit operators:

ALTER TABLE users 
ADD COLUMN flags integer DEFAULT 0;

UPDATE users 
SET flags = flags | power(2, 0)
WHERE <first condition>;

UPDATE users
SET flags = flags | power(2, 1) 
WHERE <second condition>; 

Check the bitmap using bit operators like & and |.

Materialized Boolean Views

Performance intensive boolean queries can be pre-calculated using materialized views:

CREATE MATERIALIZED VIEW verified_users AS  
SELECT * FROM users 
WHERE 
  verified = True;

REFRESH MATERIALIZED VIEW verified_users;

The view persistence avoids per-query overheads.

Industry Usage Statistics

To measure real-world usage trends, JetBrains surveyed over 1,100 SQL developers on which PostgreSQL data types they actively leverage. The results highlight that over 36% actively use boolean columns in their database schemas:

Data type	% Using Model
Serial/BigSerial (for IDs)	96%
Text/Varchar	92%
Integer	84%
Timestamp/Date	63%
Boolean	36%
JSON	34%
Array	26%

With over one-third of projects leveraging boolean values, it represents a major PostgreSQL feature. The results indicate adoption is still growing as well – uses such as audit logging, flag setting, and bit mapping represent areas of expansion.

Summary

PostgreSQL‘s boolean data type offers a robust, efficient and standardized way of working with logical true/false values. By mastering boolean column creation, inserting test values correctly, and leveraging advanced querying, PostgreSQL developers can build more accurate domain models and simplify application code.

Understanding the syntax, performance trade-offs, and use cases of leveraging boolean logic is an essential skill for any professional working with PostgreSQL-powered relational data.