Boolean type behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Boolean comparison in WHERE clause

Given the table users with a boolean column is_active, what rows will be returned by this query?

SELECT * FROM users WHERE is_active = TRUE;

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT, is_active BOOLEAN);
INSERT INTO users (name, is_active) VALUES ('Alice', TRUE), ('Bob', FALSE), ('Carol', NULL);

A Only rows where is_active is TRUE (e.g., Alice)

B Rows where is_active is TRUE or NULL (Alice and Carol)

C All rows regardless of is_active value

D Only rows where is_active is FALSE (Bob)

💡 Hint

Remember that in SQL, NULL means unknown and does not equal TRUE or FALSE.

The condition 'is_active = TRUE' filters only rows where is_active is TRUE. Rows with FALSE or NULL do not satisfy this condition.

❓ query_result

intermediate

Boolean logic with NULL values

What is the result of this query?

SELECT TRUE AND NULL AS result;

A FALSE

B NULL

C TRUE

D Syntax error

💡 Hint

Think about how SQL treats unknown values in boolean logic.

In SQL, TRUE AND NULL results in NULL because NULL represents unknown, so the result is unknown.

📝 Syntax

advanced

Correct boolean literal usage in PostgreSQL

Which option uses the correct boolean literal in PostgreSQL to insert a TRUE value?

A INSERT INTO flags (flag) VALUES ('true');

B INSERT INTO flags (flag) VALUES ('yes');

C INSERT INTO flags (flag) VALUES (1);

D INSERT INTO flags (flag) VALUES (TRUE);

💡 Hint

PostgreSQL accepts boolean literals TRUE and FALSE without quotes.

PostgreSQL boolean literals are TRUE and FALSE without quotes. Using strings or numbers may cause errors or implicit casts.

❓ query_result

advanced

Boolean expression evaluation in SELECT

What is the output of this query?

SELECT
  CASE WHEN FALSE OR NULL THEN 'Yes' ELSE 'No' END AS answer;

A 'Yes'

B NULL

C 'No'

D Syntax error

💡 Hint

Remember how OR works with NULL in SQL's three-valued logic.

FALSE OR NULL evaluates to NULL (unknown), so the CASE condition is false, returning 'No'.

🧠 Conceptual

expert

Behavior of boolean aggregates with NULLs

Consider a table checks with a boolean column passed containing TRUE, FALSE, and NULL values. What does the aggregate function bool_and(passed) return if any row has FALSE, but some rows have NULL?

A FALSE

B TRUE

C NULL

D Error

💡 Hint

bool_and returns TRUE only if all non-null inputs are TRUE.

bool_and returns FALSE if any input is FALSE, ignoring NULLs. So presence of FALSE causes FALSE result.