Chapter 5 made the database refuse duplicate keys. Here is how PostgreSQL does it.

Keys come with an index

To check a PRIMARY KEY or a UNIQUE constraint, PostgreSQL must find an existing value fast. So it creates a unique index for each one. pg_indexes shows it on a table that has no other index:

members_pkey | CREATE UNIQUE INDEX members_pkey ON public.members USING btree (id)

That is why lookups by id are fast without a CREATE INDEX:

Index Scan using members_pkey on members  (cost=0.29..8.31 rows=1 width=44)
  Index Cond: (id = 77)

CREATE UNIQUE INDEX

CREATE UNIQUE INDEX makes an index that also refuses duplicate values:

CREATE UNIQUE INDEX members_username_key ON members (username);

If the column already holds a duplicate, the index is not created:

ERROR: could not create unique index "members_username_key"
DETAIL: Key (username)=(monster77) is duplicated.

Fix the data, then run it again. After that, a duplicate write fails:

ERROR: duplicate key value violates unique constraint "members_username_key"
DETAIL: Key (username)=(monster77) already exists.

Constraint or index

Both refuse duplicates and both speed up lookups. Use the constraint to state a rule. Use CREATE UNIQUE INDEX when you need an expression, such as one email per person in any letter case.