Let's return to the INSERT statement
described in Section ![[*]](cross_ref_motif.gif)
on page .
We will continue to use the friend table from the previous chapter. In
Figure , we specified a value for each friend column. Suppose
now that we want to insert a new row, but do not want to supply
data for all columns. That is, we want to insert information about Mark,
but we do not know Mark's age.
Figure shows this scenario.
test=> INSERT INTO friend (firstname, lastname, city, state)
test-> VALUES ('Mark', 'Middleton', 'Indianapolis', 'IN');
INSERT 19074 1
After the table name, column names appear in parentheses. These columns will be assigned, in order, to the supplied data values. If we were supplying data for all columns, we would not need to name them. In this example, however, we must name the columns. The table has five columns, but we are supplying only four data values.
The column we did not assign was age. The interesting question is, ``What is in the age cell for Mark?'' The answer is that the age cell contains a NULL value.
NULL is a special value that is valid in any column. You use it when a valid entry for a field is not known or not applicable. In the previous example, we wanted to add Mark to the database but did not know his age. It is difficult to imagine what numeric value could be used for Mark's age column. Zero or -1 would be strange age values. Thus, NULL is the appropriate value for his age column.
Suppose we have a spouse column. What value should be used if someone is not married? A NULL value would be the proper value. For a wedding_anniversary column, unmarried people would have a NULL value in that field. NULL values are very useful. Before databases supported NULL values, users would put special values in columns, such as -1 for unknown numbers and 1/1/1900 for unknown dates. NULL values offer a more consistent way to mark such values.
NULL values exhibit special behavior in comparisons. Look at Figure .
test=> SELECT * FROM friend ORDER BY age DESC;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Cindy | Anderson | Denver | CO | 23
Sam | Jackson | Allentown | PA | 22
Mike | Nichols | Tampa | FL | 20
Mark | Middleton | Indianapolis | IN |
(4 rows)
test=> SELECT * FROM friend WHERE age > 0 ORDER BY age DESC;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Cindy | Anderson | Denver | CO | 23
Sam | Jackson | Allentown | PA | 22
Mike | Nichols | Tampa | FL | 20
(3 rows)
test=> SELECT * FROM friend WHERE age <> 99 ORDER BY age DESC;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Cindy | Anderson | Denver | CO | 23
Sam | Jackson | Allentown | PA | 22
Mike | Nichols | Tampa | FL | 20
(3 rows)
test=> SELECT * FROM friend WHERE age IS NULL ORDER BY age DESC;
firstname | lastname | city | state | age
-----------------+----------------------+-----------------+-------+-----
Mark | Middleton | Indianapolis | IN |
(1 row)
First, notice that the age column for Mark is empty. It is really a NULL. In the next query, because NULL values are unknown, the NULL row does not appear in the output. The third query often confuses people.9.3 Why doesn't the Mark row appear? The age is NULL or unknown, meaning that the database does not know if it equals 99--and does not guess. It refuses to print it. In fact, no comparison exists that will produce the NULL row, except the last query shown.
The tests IS NULL and IS NOT NULL are designed specifically to test for the existence of NULL values. If you are making comparisons on columns that might contain NULL values, you must test for them specifically.
Figure shows an example of such a comparison.
We have inserted Jack, but the city and state were not
known, so they are set to NULL.
test=> INSERT INTO friend
test-> VALUES ('Jack', 'Burger', NULL, NULL, 27);
INSERT 19075 1
test=> SELECT * FROM friend WHERE city = state;
firstname | lastname | city | state | age
-----------+----------+------+-------+-----
(0 rows)
The next query's WHERE comparison is contrived, but illustrative. Because city and state are both NULL, you might suspect that the Jack row would be returned. However, because NULL means unknown, we have no way to know whether the two NULL values are equal. Again, POSTGRESQL does not guess and does not print the result.
One other issue with NULLs needs clarification. In character columns,
a NULL is not the same as a zero-length value. The empty string ''
and NULL are different. Figure shows
an example highlighting this difference.
test=> CREATE TABLE nulltest (name CHAR(20), spouse CHAR(20));
CREATE
test=> INSERT INTO nulltest VALUES ('Andy', '');
INSERT 19086 1
test=> INSERT INTO nulltest VALUES ('Tom', NULL);
INSERT 19087 1
test=> SELECT * FROM nulltest ORDER BY name;
name | spouse
----------------------+----------------------
Andy |
Tom |
(2 rows)
test=> SELECT * FROM nulltest WHERE spouse = '';
name | spouse
----------------------+----------------------
Andy |
(1 row)
test=> SELECT * FROM nulltest WHERE spouse IS NULL;
name | spouse
----------------------+--------
Tom |
(1 row)
There are no valid numeric and date blank values, but a character string can be blank. When viewed in psql, any blank numeric field must contain a NULL because no blank number exists. However, there are blank strings, so blank strings and NULL values are displayed in the same way in psql. Of course, they are not the same, so be careful not to confuse the meaning of NULL values in character fields.