Boolean Values

A Boolean value is a simple data structure which can only represent values of true or false. PostgreSQL supports the SQL99-defined boolean data type, with a PostgreSQL-specific alias of bool.

Like all other data types, Boolean values can also be set to NULL. If a Boolean is set to NULL, it will never be interpreted as either true or false; it will be interpreted as NULL. This may seem obvious, but it is significant in situations where you may think to check for NULL Booleans by checking for false values (which won't work). You must use IS NULL to check for NULL Booleans. The ability to be true, false, or NULL (and its related rules regarding the designation of NULL as not being true or false) is known as three-valued logic.

Table 3-11 shows the valid constant values for a true or false state that are recognized by PostgreSQL. Which convention you choose to employ is dependent solely on your own preference. All variations of true, as well as all variations of false, are interpreted identically by the server.

Example 3-17 creates a simple table named daily_inventory that logs what books are stock and which are not, correlating an ISBN number with a Boolean value. Once created, the table is populated with data via a series of INSERT statements involving a string constant (the ISBN number), and a variety of valid Boolean constants.

booktown=# 
CREATE TABLE daily_inventory (isbn text, in_stock boolean);

CREATE
booktown=# 
INSERT INTO daily_inventory VALUES ('0385121679', true);

INSERT 3390926 1
booktown=# 
INSERT INTO daily_inventory VALUES ('039480001X', 't');

INSERT 3390927 1
booktown=# 
INSERT INTO daily_inventory VALUES ('044100590X', 'true');

INSERT 3390928 1
booktown=# 
INSERT INTO daily_inventory VALUES ('0451198492', false);

INSERT 3390929 1
booktown=# 
INSERT INTO daily_inventory VALUES ('0394900014', '0');

INSERT 3390930 1
booktown=# 
INSERT INTO daily_inventory VALUES ('0441172717', '1');

INSERT 3390931 1
booktown=# 
INSERT INTO daily_inventory VALUES ('0451160916');

INSERT 3390932 1

Now that the table has been populated with records, a SELECT query may be issued to easily check which books are in stock, as shown in Example 3-18.

booktown=# 
SELECT * FROM daily_inventory WHERE in_stock = 'yes';

    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0441172717 | t
(4 rows)

With a Boolean column you have the ability to imply a true value by referencing the column name without any kind of operator or modifying keyword. This can lead to more intuitive looking queries for well-designed tables, as shown in Example 3-19.

booktown=# 
SELECT * FROM daily_inventory WHERE in_stock;

    isbn    | in_stock
------------+----------
 0385121679 | t
 039480001X | t
 044100590X | t
 0441172717 | t
(4 rows)

Although the second query does not specify 'true' or 'false', it implicitly looks for a value of 'true' by omitting a comparison operator.

Similarly, if you want to search for false values, you may either compare the named column's value against any of the valid boolean constants in Table 3-11, or you may use the SQL keyword NOT just before the column name. Each method is demonstrated in Example 3-20.

booktown=# 
SELECT * FROM daily_inventory WHERE in_stock = 'no';

    isbn    | in_stock
------------+----------
 0451198492 | f
 0394900014 | f
(2 rows)

booktown=# 
SELECT * FROM daily_inventory WHERE NOT in_stock;

    isbn    | in_stock
------------+----------
 0451198492 | f
 0394900014 | f
(2 rows)

In this way, you can see how SQL was designed with human readability in mind. By naming your tables and columns in well-designed terms, a SQL query can read almost as plainly as an English sentence.

For the more programming-oriented readers, it may be of interest that you can use the inequality (!=) operator to compare the value of a boolean field against any of the values in Table 3-11 (e.g., WHERE in_stock != 't'). As such, the following three syntactic variations are each equivalent:

 SELECT * FROM daily_inventory WHERE NOT in_stock;
 SELECT * FROM daily_inventory WHERE in_stock = 'no';
 SELECT * FROM daily_inventory WHERE in_stock != 't';

You may have noticed that while seven rows were inserted into the table in Example 3-17, only six rows were returned between the books found in stock, and those found out of stock. This is due to the last insertion in Example 3-17 not supplying a value at all for the in_stock column, leaving the record for the book with ISBN 0451160916 with a NULL value in the in_stock column.

As stated previously, NULL will not register as either true or false. As such, you may use the SQL phrase IS NULL to check for rows with NULL values. Alternatively, you may use != but you will risk portability issues with other databases. The following syntax demonstrates a SQL query which uses the IS NULL phrase:

booktown=# 
SELECT * FROM daily_inventory WHERE in_stock IS NULL;

    isbn    | in_stock
------------+----------
 0451160916 |
(1 row)

Since IS NULL is a general SQL phrase, you can use the same WHERE clause in an UPDATE statement to correct any accidental NULL values.

booktown=# 
UPDATE daily_inventory SET in_stock = 'f' WHERE in_stock IS NULL;

UPDATE 1