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

A lack of a value at all in a boolean column is, as documented in the section called NULL Values , called NULL , which is not the same as false .

Table 3-10 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, and are interpretted identically by the server.

Table 3-10. Supported True or False Constants

True

False

true

false

't'

'f'

'true'

'false'

'y'

'n'

'yes'

'no'

'1'

'0'

Example 3-18 creates a simple table named daily_inventory which 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.

Example 3-18. Simple Boolean Table

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-19 .

Example 3-19. Checking Boolean Values

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-20 .

Example 3-20. Implying Boolean 'true'

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-10 , or you may use the SQL keyword NOT just before the column name. Each method is demonstrated in Example 3-21 .

Example 3-21. Checking for 'false' Boolean Values

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.

Warning Caution with Boolean Constants
 

If you decide to use the constants listed in Table 3-10 , every value (except for true and false ) must be enclosed within single quotes. Failure to do so will result in a server error.

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-10 (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, 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-18 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.

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, as shown in Example 3-22 .

Example 3-22. Correcting Null Values

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