8.1 boolean: true, false, and NULL Semantics

Alright, let’s talk about the boolean type. It seems simple, right? true or false. On/off. Yes/no. What could possibly go wrong? Well, you’d be surprised. This is SQL, after all, and it has a special talent for taking simple concepts and adding a dash of existential dread in the form of a third value: NULL.

Let’s get the basics out of the way. You declare a boolean column just like any other type. You can insert the literal keywords TRUE and FALSE. But here’s the first “clever” thing PostgreSQL does: it has a whole list of “truthy” and “falsy” inputs it will gladly accept, because why not? It’s the friendly, accommodating type.

CREATE TABLE task_completion (
    id serial PRIMARY KEY,
    task_name TEXT NOT NULL,
    is_complete BOOLEAN NOT NULL -- We'll get to this NOT NULL in a moment. Brace yourself.
);

INSERT INTO task_completion (task_name, is_complete)
VALUES
  ('Read the manual', TRUE),
  ('Ignore the manual', 'yes'), -- Seriously, this works.
  ('Panic', 'on'), -- Yep.
  ('Give up', 1); -- Because of course the integer 1 is true.

Go on, run that. It’s absurd, but it works. The accepted inputs are TRUE, t, true, y, yes, on, 1 and their negative counterparts. It’s a festival of permissiveness. My advice? Stick to the standard TRUE and FALSE keywords in your code. It’s clearer and won’t make the next person maintaining your code wonder if they’re having a stroke.

The Three-Valued Logic Nightmare

Here’s where the real fun begins. Unlike most programming languages, SQL doesn’t have simple Boolean logic. It has three-valued logic (3VL). Your possible values aren’t just true and false; they’re true, false, and NULL (which represents “unknown”).

This changes everything about how your WHERE clauses and CHECK constraints behave. The logical operators work like this:

Notice that NULL is a virus of uncertainty. If you AND something with NULL, you don’t get false, you get NULL. If you OR something with NULL, you don’t get true, you get NULL. And a WHERE clause only returns rows where the expression evaluates to true. It filters out both false and NULL.

This is the most common pitfall. Look at this:

SELECT * FROM task_completion WHERE is_complete = FALSE;

This will not return rows where is_complete is NULL. To catch both false and the unknown, you must be explicit:

SELECT * FROM task_completion WHERE is_complete IS NOT TRUE;

This will return rows where the value is FALSE or NULL. Wrap your head around that. IS NOT TRUE includes NULL. This isn’t a bug; it’s by design. A maddening, logic-twisting design.

Taming NULL with Constraints

The single best practice you can adopt with boolean columns is to ask yourself a question: “Does ‘unknown’ make sense for this piece of data?”

For most business logic, it doesn’t. “Has the user verified their email?” It’s either yes or no. An unknown state is functionally the same as “no” but introduces ambiguity and bugs. In these cases, you must use NOT NULL.

CREATE TABLE user_profile (
    id serial PRIMARY KEY,
    email TEXT NOT NULL,
    is_email_verified BOOLEAN NOT NULL DEFAULT FALSE -- This is the way.
);

By making it NOT NULL and providing a sensible DEFAULT (almost always FALSE), you’ve just eliminated a whole class of three-valued logic errors from your application. You’ve forced a decision. The column means what it says: true or false, no third option.

Save the nullable Boolean for the rare cases where the unknown state is a valid and meaningful piece of information. For example, user_consent_to_newsletter might start as NULL (we’ve never asked them), then become TRUE or FALSE after they’ve made a choice. But even here, consider if a three-state enum ('not_asked', 'granted', 'denied') might be clearer. It often is.

The boolean type is a powerful tool, but its default permissiveness is a trap. By understanding 3VL and using NOT NULL constraints aggressively, you turn it from a source of bugs into a model of clarity.