Let's return to the INSERT statement described in section
on page . In figure , we specified a value
for every column. Suppose you wanted to insert a new row, but did not want to
supply data for all the columns, i.e. you want to insert information about Mark,
but you don't know Mark's age.
Figure shows this.
Insertion of specific columns
After the table name, the columns to receive the data are given in parentheses.
If we were supplying data for all columns, I wouldn't need to name the columns
like this. In this case, we have to name the columns or the database server
wouldn't know which four column to fill with the four pieces of data. 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. It is used when
a valid entry for a field is not known or not applicable. For example, suppose
you want to add Mark to the database but you don't know his age. It is hard
to imagine what numeric value could be used in Mark's age column. Zero
or minus-one would be strange age values. NULL is the perfect value
for his age.
Suppose we had a spouse column. What value should be used if someone
is not married? A NULL value would be the proper value for that field.
If there were 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, like -1 for unknown numbers and 1/1/1900 for unknown
dates. NULLs are much clearer.
NULL have a special behavior in comparisons. Look at figure .
First, notice 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 really
confuses people.6.2 Why doesn't the Mark row appear? The age is NULL
or unknown, meaning the database doesn't know if it equals 99
or not, so it doesn't guess. It refuses to print it. In fact, there is no comparison
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. NULLs often confuse new
users. Remember, if you are making comparisons on columns that could contain
NULL values, you must test for them specifically.
Figure shows an example. We have inserted Jack,
but the city and state were not known, so they are set to NULL.
Comparison of NULL fields
The next query's WHERE comparison is contrived, but illustrative.
Because city and state are both NULL, you may suspect
that the Jack row would be returned. However, because NULL means
unknown, there is no way to know if the two NULL values are
equal. Again, POSTGRESQL doesn't guess, and refuses to print it.
There is one more issue with NULLs that needs clarification. In character
columns, a NULL is not the same as a zero length field. That means
that the string '' and NULL are different. Figure
shows an example of this.
NULLs and blank strings
There are no valid numeric and date blank values, but a character string
can be blank. When viewed in psql, any numeric field that is blank
has to contain a NULL because there is no blank number. However,
there are blank strings, so blank strings and NULLs are displayed the
same in psql. However, they are not the same, so be careful
not to confuse the meaning of NULLs in character fields.