SQL, Handling empty cells

When we create a table in this way:

CREATE TABLE people (
  age INT,
  name CHAR(20)
);

SQL freely accepts empty values as records:

INSERT INTO people VALUES (null, null);

This might be a problem, because now we have a row with null values:

 age |  name  
-----+--------
  37 | Flavio
   8 | Roger
     | 

NULL is a special marker that means “no value here”. It is not zero, and it is not an empty string. It records the absence of data: we never told the database that person’s age or name.

NULL exists because real data is incomplete. A user skips a form field, or you will only know a value later. The database needs an honest way to store “unknown” instead of forcing you to invent a fake number.

Finding NULL values

Here is the classic mistake. This query returns zero rows, even though the table clearly contains a null:

SELECT * FROM people WHERE age = NULL;

In SQL, NULL is never equal to anything, not even to another NULL. The comparison does not evaluate to true, so every row is filtered out. Use the dedicated operators instead:

SELECT * FROM people WHERE age IS NULL;
SELECT * FROM people WHERE age IS NOT NULL;

IS NULL finds the rows with missing data. IS NOT NULL finds everything else.

When you display data, COALESCE() lets you swap a null for a fallback. It returns the first non-null argument it receives:

SELECT COALESCE(name, 'unknown') FROM people;

Preventing NULL values

To solve this, we can declare constraints on our table rows. NOT NULL prevents null values:

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL
);

If we try to execute this query again:

INSERT INTO people VALUES (null, null);

We’d get an error, like this:

ERROR:  null value in column "age" violates not-null constraint
DETAIL:  Failing row contains (null, null).

The error names the exact column that failed, and nothing was written to the table. Fix the statement to provide real values and run it again.

Note that an empty string is a valid non-null value. A NOT NULL column of type CHAR still accepts ''. If empty strings are also unacceptable for your data, you need a CHECK constraint on top.