In the last lab, supplier 5 got NULL for country. Some columns must always have a value. Others have a usual value.

NOT NULL

Write NOT NULL after the type:

CREATE TABLE customers_v2 (
  id      integer NOT NULL,
  name    text    NOT NULL,
  country char(2) NOT NULL,
  city    text
);

city can be NULL. The other three columns cannot. An INSERT that leaves country out, or gives NULL, fails:

ERROR: null value in column "country" of relation "customers_v2" violates not-null constraint
DETAIL: Failing row contains (5, Mystery Maker, null, null).

NOT NULL is a constraintconstraintA rule on a table that the database checks on every new or changed row: a rule that the database checks on every new or changed row. A row that breaks the rule is refused. Chapter 5 adds more constraints.

DEFAULT

DEFAULT gives a value to a column that the INSERT leaves out:

CREATE TABLE reviews (
  id         integer NOT NULL,
  stars      integer NOT NULL,
  approved   boolean NOT NULL DEFAULT false,
  written_on date    NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO reviews (id, stars) VALUES (1, 5);

The default can be a fixed value (false, 0, 'pending') or an expression such as CURRENT_DATE. PostgreSQL calculates it for each new row.

Which value does a column get?

A NULL that you give is not replaced by the default. To ask for the default in a VALUES list, write the keyword DEFAULT:

INSERT INTO reviews (id, stars, approved) VALUES (2, 4, DEFAULT);