PostgreSQL Tutorial

  • Home
  • Administration
  • Views
  • Triggers
  • Stored Procedures
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
Home / PostgreSQL Tutorial / PostgreSQL Boolean Data Type with Practical Examples

PostgreSQL Boolean Data Type with Practical Examples

Summary: in this tutorial, you will learn about the PostgreSQL Boolean data type and how to use it in designing the database tables.

Introduction to the PostgreSQL Boolean type

PostgreSQL BooleanPostgreSQL supports a single Boolean data type: BOOLEAN that can have three states: TRUE, FALSE, and NULL. PostgreSQL uses one byte for storing a boolean value in the database. The BOOLEAN can be abbreviated as BOOL.

In standard SQL, a Boolean value can be TRUE, FALSE, or NULL. However, PostgreSQL is quite flexible when dealing with TRUE and FALSE values.  The following table shows the valid literal values for TRUE and FALSE in PostgreSQL.

TrueFalse
truefalse
‘t’‘f ‘
‘true’‘false’
‘y’‘n’
‘yes’‘no’
‘1’‘0’

Note that the leading or trailing whitespace does not matter and all the constant values except for true and false 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 stock_availability to log which product are available.

1
2
3
4
CREATE TABLE stock_availability (
product_id INT NOT NULL PRIMARY KEY,
available BOOLEAN NOT NULL
);

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

1
2
3
4
5
6
7
8
9
10
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, to check the products that are available, you use the following statement:

1
2
3
4
5
6
SELECT
*
FROM
stock_availability
WHERE
available = 'yes';

1
2
3
4
5
6
7
8
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. The following query returns all products that are available:

1
2
3
4
5
6
SELECT
*
FROM
stock_availability
WHERE
available;

Similarly, if you want to look for false values, you compare the value of the Boolean column against any valid Boolean constants. The following query returns the products that are not available.

1
2
3
4
5
6
SELECT
*
FROM
stock_availability
WHERE
available = 'no';

1
2
3
4
5
6
product_id | available
------------+-----------
        200 | f
        700 | f
        800 | f
(3 rows)

Or you can use the NOT operator to check if values in the Boolean column are false as the following example:

1
2
3
4
5
6
SELECT
*
FROM
stock_availability
WHERE
NOT available;

Set a default value of the Boolean column

To set a default value for an existing Boolean column, you use the SET EFAULT clause in the ALTER TABLE statement.

For example, the following ALTER TABLE statement set the default value for the available column in the stock_availability table:

1
2
ALTER TABLE stock_availability ALTER COLUMN available
SET DEFAULT FALSE;

If you insert a row without specifying the value for the available column, PostgreSQL uses the FALSE value by default.

1
2
3
INSERT INTO stock_availability (product_id)
VALUES
(900);

1
2
3
4
5
6
SELECT
*
FROM
stock_availability
WHERE
product_id = 900;

1
2
3
4
product_id | available
------------+-----------
        900 | f
(1 row)

Similarly, if you want to set a default value for a Boolean column when you create a table, you use the DEFAULT clause in the column definition as follows:

1
2
3
4
CREATE TABLE boolean_demo(
   ...
   is_ok BOOL DEFAULT 't'
);

In this tutorial, you have learned about the PostgreSQL BOOLEAN data type and how to use it in the database table design.

Related Tutorials

  • PostgreSQL Data Types
Previous Tutorial: Using PostgreSQL SERIAL To Create Auto-increment Column
Next Tutorial: PostgreSQL Character Types: CHAR, VARCHAR, and TEXT

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete
  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • PostgreSQL Recursive View
  • Learn PostgreSQL Recursive Query By Example
  • Creating Updatable Views Using the WITH CHECK OPTION Clause
  • PostgreSQL Upsert Using INSERT ON CONFLICT statement
  • How to Generate a Random Number in A Range
  • Using PostgreSQL ADD COLUMN to Add One or More Columns To a Table
  • PostgreSQL Character Types: CHAR, VARCHAR, and TEXT
  • Using PostgreSQL SERIAL To Create Auto-increment Column
  • PostgreSQL Boolean Data Type with Practical Examples
  • Understanding PostgreSQL Timestamp Data Types

More Tutorials

  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Functions
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2016 by PostgreSQL Tutorial Website. All Rights Reserved.