Boolean type behavior in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What values can a PostgreSQL Boolean type store?

A PostgreSQL Boolean type can store three values: true, false, and NULL (unknown).

beginner

How does PostgreSQL interpret the string 't' when cast to Boolean?

PostgreSQL interprets the string 't' as true when cast to Boolean.

intermediate

What happens when you use a Boolean column in a WHERE clause in PostgreSQL?

The WHERE clause treats true as passing the condition, false as failing, and NULL as unknown, so rows with NULL in that column are excluded unless explicitly handled.

intermediate

Can you use Boolean values in arithmetic operations in PostgreSQL?

No, Boolean values cannot be directly used in arithmetic operations. You must cast them to integers (true = 1, false = 0) first.

beginner

How do you explicitly cast a Boolean to text in PostgreSQL?

Use the CAST operator or :: syntax, for example: SELECT true::text; returns 't'.

Which of these is NOT a valid Boolean literal in PostgreSQL?

A 'xyz'

B 'true'

C 'f'

D 'false'

PostgreSQL accepts 'true', 'false', 't', 'f' as Boolean literals, but 'xyz' is not recognized as a Boolean.

What does the expression SELECT NULL::boolean; return?

A Error

B false

C true

D NULL

Casting NULL to boolean results in NULL, representing an unknown Boolean value.

In a WHERE clause, what happens to rows where the Boolean column is NULL?

A They are excluded

B They are included

C They cause an error

D They are converted to false

Rows with NULL in a Boolean column are excluded because NULL means unknown, which does not satisfy the WHERE condition.

How can you convert a Boolean true to integer 1 in PostgreSQL?

A true * 1

B true + 1

C CAST(true AS integer)

D true::text

You must explicitly cast Boolean to integer using CAST or :: syntax; arithmetic operations on Boolean are not allowed.

Which of these is the correct way to cast Boolean false to text?

A CAST(false AS integer)

B false::text

C CAST(false AS boolean)

D false + ''

Casting Boolean false to text uses false::text or CAST(false AS text).

Explain how PostgreSQL handles Boolean values and their behavior in queries.

Think about how true, false, and NULL affect filtering and casting.

Describe how to convert Boolean values to other types in PostgreSQL and why it might be necessary.

Consider when you want to display or calculate with Boolean values.