PostgreSQL Boolean Type

Summary: in this tutorial, you’ll learn how to use the PostgreSQL boolean type to store Boolean values including true and false.

Getting started with PostgreSQL boolean type #

In PostgreSQL, you use the boolean type to store Boolean data, including true and false.

The boolean type has three values:

• true
• false
• NULL

NULL represents the unknown state.

PostgreSQL uses the boolean keyword to represent the boolean type. You can also use bool, an abbreviation for the boolean type.

PostgreSQL uses 1 byte for storing a boolean value.

Besides true and false, PostgreSQL uses various representations for true and false in SQL queries:

Notice that all other values need to be surrounded by single quotes except true and false.

PostgreSQL boolean type examples #

First, create a table called products to store product data:

CREATE TABLE products (
  id INT GENERATED ALWAYS AS IDENTITY,
  name VARCHAR NOT NULL,
  is_active BOOL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The products table has the is_active column with the type boolean, so it can store true, false, or NULL.

If you do not want to store NULL in a boolean column, you can a the NOT NULL constraint.

Second, insert some rows into the products table:

INSERT INTO
  products (name, is_active)
VALUES
  ('iPhone 6', false),
  ('iPhone 16', true),
  ('Galaxy M01', 'f'),
  ('Galaxy Z Fold6', 't'),
  ('Galaxy Z Flip6', NULL) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |      name      | is_active
----+----------------+-----------
  1 | iPhone 6       | f
  2 | iPhone 16      | t
  3 | Galaxy M01     | f
  4 | Galaxy Z Fold6 | t
  5 | Galaxy Z Flip6 | NULLCode language: plaintext (plaintext)

Third, retrieve the inactive products:

SELECT * FROM products
WHERE is_active = false;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |    name    | is_active
----+------------+-----------
  1 | iPhone 6   | f
  3 | Galaxy M01 | fCode language: plaintext (plaintext)

You can use other false values like 'f', '0', 'n', or 'no'. For example:

SELECT * FROM products
WHERE is_active = 'f';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Fourth, query the active products from the products table:

SELECT * FROM products
WHERE is_active = true;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |      name      | is_active
----+----------------+-----------
  2 | iPhone 16      | t
  4 | Galaxy Z Fold6 | tCode language: plaintext (plaintext)

Implicit comparisons with Booleans #

PostgreSQL allows you to use the column name only to compare it with true implicitly. For example, the following statement retrieves active products:

SELECT * FROM products
WHERE is_active;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |      name      | is_active
----+----------------+-----------
  2 | iPhone 16      | t
  4 | Galaxy Z Fold6 | tCode language: plaintext (plaintext)

To negate the value of a column, you can use the NOT operator:

SELECT * FROM products
WHERE NOT is_active;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id |    name    | is_active
----+------------+-----------
  1 | iPhone 6   | f
  3 | Galaxy M01 | fCode language: plaintext (plaintext)

Setting default values for Boolean columns #

You use the DEFAULT constraint to set a default value for a boolean column:

column_name BOOL DEFAULT boolean_value;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement creates the users table with the column email_confirmed set to false by default:

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY,
  email VARCHAR NOT NULL,
  password VARCHAR NOT NULL,
  email_confirmed BOOL NOT NULL DEFAULT false
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

• Use the PostgreSQL boolean type to store Boolean values.
• PostgreSQL uses implicit comparisons for Boolean values.

Quiz #