How to allow only one row for a table?

Question

I have one table in which I would like only one entry. So if someone is trying to insert another row it shouldn't be allowed, only after someone deleted the previously existing row.

How do I set a rule for a table like this?

Answer 1

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;