Columns that refuse what should never be stored

Difficulty 2/5Subscribers

New to this? Learn it first: Types that tell the truth New to this? Learn it first: NULL: the value that is not a value New to this? Learn it first: CHECK: rules a row must keep New to this? Learn it first: Level 1 boss: the signup system

A marketplace stores reviews of completed purchases.

Each review has three fields, and each has a rule the business already treats as non-negotiable:

A rating from 1 to 5. Whole stars only: no 3.5, no 0, no 7.
The amount paid for the purchase being reviewed. It is money: two decimals, and it has to add up exactly. A cent lost to rounding is a bug Finance will find.
When the review was posted. Reviewers are in different countries, and "which day was that" has to have one answer for everyone.

Plus the obvious: a review belongs to a purchase that exists, and the same purchase is reviewed at most once.

purchases already exists.

The contract

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

purchases
id
reviews
idpurchase_idratingamount_paidposted_at

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE purchases (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

INSERT INTO purchases DEFAULT VALUES;
INSERT INTO purchases DEFAULT VALUES;
INSERT INTO purchases DEFAULT VALUES;
INSERT INTO purchases DEFAULT VALUES;

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…