Understanding the Boolean Type in PostgreSQL

In SQL (and computer science in general), a Boolean expression is a logical statement that evaluates to either true or false. Some RDBMSs provide a boolean data type that can store values that are either true or false (or unknown). PostgreSQL is one such RDBMS.

In PostgreSQL, the Boolean type is called boolean and it uses 1 byte.

Possible Values

In PostgreSQL, the boolean data type can be in one of three states; true, false, or unknown. Usually it’s either true or false, but if it’s unknown then it will be represented by null.

Boolean constants can be represented in queries by the keywords TRUE, FALSE, and NULL.

String representations of boolean values are case-insensitive (for example, true and TRUE are equivalent).

The `TRUE` State

The true state can be represented by any of the following string representations:

true
yes
on
1

These can be uppercase or lowercase. So the following list is equivalent to the previous:

TRUE
YES
ON
1

And it’s possible to use the unique prefix such as t for true:

t
y

And their uppercase equivalents:

T
Y

The `FALSE` State

The false state can be represented by any of the following string representations:

false
no
off
0

These can be uppercase or lowercase. So the following list is equivalent to the previous:

FALSE
NO
OFF
1

And it’s possible to use the unique prefix such as f for false:

f
n

And their uppercase equivalents:

F
N

Boolean Constants

We can write Boolean constants for the true/false state with the keywords TRUE and FALSE, but we can’t do this with NULL. If we need to use NULL, then we need to cast it explicitly. For example:

SELECT NULL::boolean;

Result:

 bool 
------
 null
(1 row)

It’s also possible to explicitly cast other values as boolean:

\x
SELECT 
    'true'::boolean AS "'true'",
    'TRUE'::boolean AS "'TRUE'",
    't'::boolean AS "'t'",
    'T'::boolean AS "'T'",
    'f'::boolean AS "'f'",
    'F'::boolean AS "'F'",
    'y'::boolean AS "'y'",
    'Y'::boolean AS "'Y'",
    'n'::boolean AS "'n'",
    'N'::boolean AS "'N'",
    'on'::boolean AS "'on'",
    'ON'::boolean AS "'ON'",
    'off'::boolean AS "'off'",
    'OFF'::boolean AS "'OFF'",
    '1'::boolean AS "'1'",
    '0'::boolean AS "'0'",
    1::boolean AS "1",
    0::boolean AS "0";

Result (using vertical output):

'true' | t
'TRUE' | t
't'    | t
'T'    | t
'f'    | f
'F'    | f
'y'    | t
'Y'    | t
'n'    | f
'N'    | f
'on'   | t
'ON'   | t
'off'  | f
'OFF'  | f
'1'    | t
'0'    | f
1      | t
0      | f

Creating a Boolean Database Column

In PostgreSQL, we can define database columns as boolean. When we do this, the column accepts only true, false, or null values.

Here’s an example of creating a database table with a Boolean column:

CREATE TABLE dogs (
    dog_name text, 
    good_dog boolean
    );

In this case, the good_dog column is a boolean column. Therefore a dog can be either a good dog or not a good dog.

Inserting Boolean Data

We can insert Boolean data as TRUE, FALSE or NULL. We can also use ON, OFF, etc. In some cases we may need to explicitly cast a value using the ::boolean syntax as shown in the example above.

Let’s insert Boolean data into our table and select it:

INSERT INTO dogs VALUES ('Wag', TRUE);
INSERT INTO dogs VALUES ('Bark', FALSE);
INSERT INTO dogs VALUES ('Sausage', NULL);
INSERT INTO dogs (dog_name) VALUES ('Pant');

SELECT * FROM dogs;

Result:

 dog_name | good_dog 
----------+----------
 Wag      | t
 Bark     | f
 Sausage  | null
 Pant     | null

When using psql, Boolean columns return t, f, or null (even if we insert the value as TRUE, FALSE, YES, NO, etc).

Regarding the null values, in this example, one of the rows was inserted with an explicit NULL, and the other one became null because I omitted a value for that row.

Let’s insert more data, this time using various other keywords:

INSERT INTO dogs VALUES ('Fluffy', 'on');
INSERT INTO dogs VALUES ('Dribble', 'off');
INSERT INTO dogs VALUES ('Fetch', 'yes');
INSERT INTO dogs VALUES ('Drop', 'no');
INSERT INTO dogs VALUES ('Rover', 1::boolean);
INSERT INTO dogs VALUES ('Rufus', 0::boolean);

SELECT * FROM dogs;

Result:

 dog_name | good_dog 
----------+----------
 Wag      | t
 Bark     | f
 Sausage  | null
 Pant     | null
 Fluffy   | t
 Dribble  | f
 Fetch    | t
 Drop     | f
 Rover    | t
 Rufus    | f

Once again we can see that the values are returned as either t or f, regardless of the form we used when inserting the data.

Querying Boolean Columns

It’s possible to query Boolean columns like this:

SELECT * FROM dogs WHERE good_dog;

Result:

 dog_name | good_dog 
----------+----------
 Wag      | t
 Fluffy   | t
 Fetch    | t
 Rover    | t

Notice the WHERE clause only has the Boolean column and nothing else – it goes WHERE good_dog and nothing else. There’s no equality operator or anything. I didn’t specify whether the column should be true or false, I simply used the name of the column.

When we do this on a Boolean column, we’re implicitly stating that it should be true. It’s basically the same as doing WHERE good_dog = TRUE or even WHERE good_dog IS TRUE.

So we could run any of the following queries to get the same result:

SELECT * FROM dogs WHERE good_dog IS TRUE;
SELECT * FROM dogs WHERE good_dog = TRUE;
SELECT * FROM dogs WHERE good_dog = 1::boolean;
SELECT * FROM dogs WHERE good_dog = '1';
SELECT * FROM dogs WHERE good_dog = 'on';
SELECT * FROM dogs WHERE good_dog = 'yes';

Notice that I had to enclose some of these in quotes, otherwise I’d get an error. Quotes aren’t required when using the TRUE/FALSE/NULL keywords though.

We can also use the NOT operator to negate the operation:

SELECT * FROM dogs WHERE NOT good_dog;

Result:

 dog_name | good_dog 
----------+----------
 Bark     | f
 Dribble  | f
 Drop     | f
 Rufus    | f

This time the only results returned are those where the good_dog column is FALSE. It didn’t return the TRUE rows or the null rows.

Of course, we can also use the NOT operator to negate the other variations, and we can also use other operators such as the not equal to operator (<>):

SELECT * FROM dogs WHERE good_dog <> TRUE;

Result:

 dog_name | good_dog 
----------+----------
 Bark     | f
 Dribble  | f
 Drop     | f
 Rufus    | f

It’s even possible to use the greater than operator (>) and the less than operator (<):

SELECT * FROM dogs WHERE good_dog < TRUE;

Result:

 dog_name | good_dog 
----------+----------
 Bark     | f
 Dribble  | f
 Drop     | f
 Rufus    | f

Although these later examples aren’t typical of how most developers test Boolean values, given a Boolean value is either on or off (or unknown).

As with any other column, we can test for null values by using IS NULL or IS NOT NULL:

SELECT * FROM dogs WHERE good_dog IS NULL;

Result:

 dog_name | good_dog 
----------+----------
 Sausage  | null
 Pant     | null

And:

SELECT * FROM dogs WHERE good_dog IS NOT NULL;

Result:

 dog_name | good_dog 
----------+----------
 Wag      | t
 Bark     | f
 Fluffy   | t
 Dribble  | f
 Fetch    | t
 Drop     | f
 Rover    | t
 Rufus    | f

Possible Values

The TRUE State

The FALSE State

Boolean Constants

Creating a Boolean Database Column

Inserting Boolean Data

Querying Boolean Columns

The `TRUE` State

The `FALSE` State