Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE simple21_page (
    id integer NOT NULL,
    name character varying(120) NOT NULL,
    text text NOT NULL,
    parent_id integer
);

CREATE TABLE

CREATE UNIQUE INDEX ON simple21_page(COALESCE(parent_id, -1));

CREATE INDEX

INSERT INTO simple21_page VALUES (1, 'a', 'text', NULL)

INSERT 0 1

INSERT INTO simple21_page VALUES (2, 'b', 'text', NULL)

ERROR:  duplicate key value violates unique constraint "simple21_page_coalesce_idx"
DETAIL:  Key (COALESCE(parent_id, '-1'::integer))=(-1) already exists.