Types, NOT NULL and UNIQUE do not stop every bad value. A price of -5.00 is a valid number. Stars of 9 is a valid integer. CHECK adds a condition.

CHECK on a column

CREATE TABLE products (
  id    integer PRIMARY KEY,
  name  text NOT NULL,
  price numeric(8,2) NOT NULL CHECK (price >= 0)
);

The condition is the same as a condition in WHERE (Chapter 2). The database tests it on every new or changed row:

ERROR: new row for relation "products" violates check constraint "products_price_check"
DETAIL: Failing row contains (14, Broken Mug, -5.00).

More conditions:

CHECK (stars BETWEEN 1 AND 5)
CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
CHECK (length(code) = 8)

A CHECK on several columns

Write it after the columns. It can compare columns of the same row:

CREATE TABLE sales (
  id        integer PRIMARY KEY,
  starts_on date NOT NULL,
  ends_on   date NOT NULL,
  CHECK (ends_on >= starts_on)
);

NULL passes a CHECK

WHERE keeps a row only when the condition is TRUE. CHECK refuses a row only when the condition is FALSE. For NULL, price >= 0 is UNKNOWN, so the row passes.

Add NOT NULL when the value is required.

Name your constraints

Every constraint has a name. PostgreSQL makes one, such as products_price_check. Choose your own with CONSTRAINT name before the rule:

price numeric(8,2) NOT NULL CONSTRAINT price_not_negative CHECK (price >= 0)

The error then says violates check constraint "price_not_negative". The same works for UNIQUE and PRIMARY KEY: CONSTRAINT coupons_code_unique UNIQUE (code).