Boolean type behavior in PostgreSQL - Mini Project: Build & Apply

Boolean Type Behavior in PostgreSQL

📖 Scenario: You are managing a simple task tracking system. Each task can be marked as completed or not completed using a boolean value.

🎯 Goal: Build a PostgreSQL table to store tasks with a boolean column indicating completion status. Then, write queries to insert tasks, update their status, and select tasks based on their completion.

📋 What You'll Learn

Create a table named tasks with columns id (integer primary key), description (text), and completed (boolean).

Insert three tasks with specific descriptions and completion statuses.

Write a query to select all tasks that are not completed.

Write a query to update a task's completion status to true.

💡 Why This Matters

🌍 Real World

Boolean columns are commonly used in databases to track yes/no, true/false, or completed/not completed states for records.

💼 Career

Understanding boolean data types and how to query and update them is essential for database management and backend development roles.

1

Create the tasks table

Write a SQL statement to create a table called tasks with three columns: id as an integer primary key, description as text, and completed as a boolean.

PostgreSQL

-- Create the tasks table with id, description, and completed columns
-- Your code here

Hint

Use CREATE TABLE with the specified column names and types.

2

Insert tasks with boolean completion status

Insert three rows into the tasks table with these exact values: (1, 'Buy groceries', false), (2, 'Clean the house', true), and (3, 'Pay bills', false).

PostgreSQL

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    description TEXT,
    completed BOOLEAN
);
-- Insert the three tasks below
-- Your code here

Hint

Use a single INSERT INTO statement with multiple rows.

3

Select tasks that are not completed

Write a SQL query to select all columns from tasks where the completed column is false.

PostgreSQL

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    description TEXT,
    completed BOOLEAN
);

INSERT INTO tasks (id, description, completed) VALUES
(1, 'Buy groceries', false),
(2, 'Clean the house', true),
(3, 'Pay bills', false);

-- Select tasks where completed is false
-- Your code here

Hint

Use WHERE completed = false to filter tasks not done.

4

Update a task's completion status

Write a SQL statement to update the completed column to true for the task with id equal to 3.

PostgreSQL

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    description TEXT,
    completed BOOLEAN
);

INSERT INTO tasks (id, description, completed) VALUES
(1, 'Buy groceries', false),
(2, 'Clean the house', true),
(3, 'Pay bills', false);

SELECT * FROM tasks WHERE completed = false;

-- Update task with id 3 to completed true
-- Your code here

Hint

Use UPDATE with SET completed = true and a WHERE clause for id = 3.

Practice

1. In PostgreSQL, which of the following is a valid boolean value stored in a boolean column?

easy

A. TRUE

B. 2

C. 'foo'

D. 'NULL'

Solution

1. Step 1: Understand boolean data type values

   PostgreSQL boolean columns accept TRUE, FALSE, or NULL (unknown).

2. Step 2: Identify valid boolean literals

   TRUE and FALSE are valid boolean literals; 2, 'foo', and 'NULL' are not valid boolean literals in PostgreSQL.

3. Final Answer:

   TRUE -> Option A

4. Quick Check:

   Boolean values = TRUE or FALSE [OK]

Hint: Boolean columns accept TRUE, FALSE, or NULL only [OK]

Common Mistakes:

• Using 1 or 0 as boolean values directly
• Using strings like 'yes' or 'no' as boolean
• Confusing NULL with a boolean TRUE or FALSE

2. Which of the following is the correct way to declare a boolean column named is_active in a PostgreSQL table?

easy

A. CREATE TABLE users (is_active BOOLEAN(1));

B. CREATE TABLE users (is_active BOOLEAN);

C. CREATE TABLE users (is_active BOOL(1));

D. CREATE TABLE users (is_active BOOLIAN);

Solution

1. Step 1: Check correct boolean type syntax

   PostgreSQL uses BOOLEAN or BOOL as valid types; BOOLEAN(1) is invalid.

2. Step 2: Identify syntax errors

   A uses invalid BOOLEAN(1) syntax; B has a typo 'BOOLIAN'; C uses invalid BOOL(1) syntax.

3. Final Answer:

   CREATE TABLE users (is_active BOOLEAN); -> Option B

4. Quick Check:

   BOOLEAN keyword is correct type [OK]

Hint: Use BOOLEAN or BOOL, no size or typo allowed [OK]

Common Mistakes:

• Typo in BOOLEAN keyword
• Adding size like BOOLEAN(1)
• Using unsupported types like BOOLIAN

3. Given the table settings with a boolean column enabled, what will this query return?

SELECT enabled FROM settings WHERE enabled = FALSE;

medium

A. All rows where enabled is TRUE

B. All rows where enabled is NULL

C. All rows where enabled is FALSE

D. Syntax error

Solution

1. Step 1: Understand the WHERE clause condition

   The query filters rows where the boolean column enabled equals FALSE.

2. Step 2: Determine which rows match

   Only rows with enabled = FALSE will be returned; TRUE or NULL rows are excluded.

3. Final Answer:

   All rows where enabled is FALSE -> Option C

4. Quick Check:

   WHERE enabled = FALSE returns FALSE rows [OK]

Hint: WHERE boolean_column = FALSE returns only FALSE rows [OK]

Common Mistakes:

• Assuming NULL rows are included
• Confusing TRUE and FALSE values
• Thinking it returns all rows

4. You wrote this query but it returns no rows:

SELECT * FROM users WHERE is_admin = TRUE;

The is_admin column is boolean but some rows have NULL. What is the likely issue?

medium

A. TRUE is not a valid boolean value in PostgreSQL

B. The query syntax is incorrect

C. The column should be text, not boolean

D. NULL values cause the condition to fail, so no rows with NULL appear

Solution

1. Step 1: Understand boolean NULL behavior in WHERE clause

   Comparing boolean column to TRUE excludes NULL rows because NULL means unknown, not TRUE.

2. Step 2: Explain why no rows returned

   Rows with NULL in is_admin do not satisfy is_admin = TRUE, so they are excluded.

3. Final Answer:

   NULL values cause the condition to fail, so no rows with NULL appear -> Option D

4. Quick Check:

   NULL in boolean = TRUE returns no rows [OK]

Hint: NULL in boolean columns excludes rows in equality checks [OK]

Common Mistakes:

• Thinking NULL equals TRUE
• Assuming TRUE is invalid boolean
• Believing syntax error caused no rows

5. You want to select all rows from tasks where the boolean column completed is either FALSE or NULL. Which query correctly does this?

hard

A. SELECT * FROM tasks WHERE completed = FALSE OR completed IS NULL;

B. SELECT * FROM tasks WHERE completed = FALSE AND completed IS NULL;

C. SELECT * FROM tasks WHERE NOT completed;

D. SELECT * FROM tasks WHERE completed != TRUE;

Solution

1. Step 1: Understand boolean NULL and FALSE filtering

   To get rows where completed is FALSE or NULL, use OR with completed = FALSE and completed IS NULL.

2. Step 2: Analyze each option

   SELECT * FROM tasks WHERE completed = FALSE AND completed IS NULL; uses AND, which cannot be both FALSE and NULL simultaneously. SELECT * FROM tasks WHERE NOT completed; excludes NULL rows. SELECT * FROM tasks WHERE completed != TRUE; excludes TRUE and NULL (since NULL != TRUE is unknown), includes only FALSE.

3. Final Answer:

   SELECT * FROM tasks WHERE completed = FALSE OR completed IS NULL; -> Option A

4. Quick Check:

   Use OR to include FALSE or NULL rows [OK]

Hint: Use OR with IS NULL to include unknown boolean values [OK]

Common Mistakes:

• Using AND instead of OR
• Ignoring NULL rows in boolean filters
• Using NOT completed excludes NULL rows