NULL values represent missing, unknown, or not-applicable values. For example, let's say that you want to add a membership_expiration_date to the customers table. Some customers might be permanent memberstheir memberships will never expire. For those customers, the membership_expiration_date is not applicable and should be set to NULL. You may also find some customers who don't want to provide you with their birth dates. The birth_date column for these customers should be NULL.
In one case, NULL means not applicable. In the other case, NULL means don't know. A NULL membership_expiration_date does not mean that you don't know the expiration date, it means that the expiration date does not apply. A NULL birth_date does not mean that the customer was never born(!); it means that the date of birth is unknown.
Of course, when you create a table, you can specify that a given column cannot hold NULL values (NOT NULL). When you do so, you aren't affecting the data type of the column; you're just saying that NULL is not a legal value for that particular column. A column that prohibits NULL values is mandatory; a column that allows NULL values is optional.
You may be wondering how a data type could hold all values legal for that type, plus one more value. The answer is that PostgreSQL knows whether a given column is NULL not by looking at the column itself, but by first examining a NULL indicator (a single bit) stored separately from the column. If the NULL indicator for a given row/column is set to TRUE, the data stored in the row/column is meaningless. This means that a data row is composed of values for each column plus an array of indicator bitsone bit for each optional column.
Part I: General PostgreSQL Use
Introduction to PostgreSQL and SQL
Working with Data in PostgreSQL
PostgreSQL SQL Syntax and Use
Part II: Programming with PostgreSQL
Introduction to PostgreSQL Programming
The PostgreSQL C APIlibpq
A Simpler C APIlibpgeasy
The New PostgreSQL C++ APIlibpqxx
Embedding SQL Commands in C Programsecpg
Using PostgreSQL from an ODBC Client Application
Using PostgreSQL from a Java Client Application
Using PostgreSQL with Perl
Using PostgreSQL with PHP
Using PostgreSQL with Tcl and Tcl/Tk
Using PostgreSQL with Python
Npgsql: The .NET Data Provider
Other Useful Programming Tools
Part III: PostgreSQL Administration
Introduction to PostgreSQL Administration
Internationalization and Localization
Replicating PostgreSQL Data with Slony