Полезная информация

next up previous contents index
Next: Controlling DEFAULT Values Up: Customizing Queries Previous: Quotes Inside Text

Using NULL Values

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.

  
Figure: Insertion of specific columns
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...iddleton',~'Indianapolis',~'IN');
\par INSERT~18881~1\end{list}\par
\end{figure}

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 [*].

  
Figure: NULL handling
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...\vert Indianapolis~~~\vert IN~~~\vert~~~
\par (1~row)\end{list}\par
\end{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.

  
Figure: Comparison of NULL fields
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...{}-{}-+-{}-{}-{}-+-{}-{}-{}-{}-+-{}-{}-
\par (0~rows)\end{list}\par
\end{figure}

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.

  
Figure: NULLs and blank strings
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...{}-
\par Tom~~~~~~~~~~~~~~~~~\vert~~~~~~
\par (1~row)\end{list}\par
\end{figure}

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.


next up previous contents index
Next: Controlling DEFAULT Values Up: Customizing Queries Previous: Quotes Inside Text
Bruce Momjian
1999-11-21