How to allow only one row for a table?

A UNIQUE constraint allows multiple rows with null values, because two null values are not considered to be the same. (Except when using NULLS NOT DISTINCT in Postgres 15 or later.)

Similar considerations apply to CHECK constraints. They allow the expression to be true or null (just not false). Again, null values get past the check.

To rule that out, define the column NOT NULL. Or make it the PRIMARY KEY since PK columns are defined NOT NULL automatically. See:

• Why can I create a table with PRIMARY KEY on a nullable column?

Also, just use boolean:

CREATE TABLE public.onerow (
   onerow_id bool PRIMARY KEY DEFAULT true
 , data text
 , CONSTRAINT onerow_uni CHECK (onerow_id)
);

The CHECK constraint can be that simple for a boolean column. Only true is allowed.

You may want to REVOKE (or not GRANT) the DELETE and TRUNCATE privileges from public (and all other roles) to prevent the single row from ever being deleted. Like:

REVOKE DELETE, TRUNCATE ON public.onerow FROM public;