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

Because the PRIMARY KEY makes the included column(s) NOT NULL automatically. I quote the manual here:

The primary key constraint specifies that a column or columns of atable can contain only unique (non-duplicate), nonnull values.Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL.

Bold emphasis mine.

I ran a test to confirm that NOT NULL is completely redundant in combination with a PRIMARY KEY constraint (in the current implementation, retested in version 13). The NOT NULL constraint stays even after dropping the PK constraint, irregardless of an explicit NOT NULL clause at creation time.

CREATE TABLE foo (foo_id int PRIMARY KEY);ALTER TABLE foo DROP CONSTRAINT foo_pkey;

db=# \d foo   table »public.foo« column |  type   | attribute--------+---------+----------- foo_id | integer | not null    -- stays

db<>fiddle here

Identical behavior if NULL is included in the CREATE TABLE statement.

It still won't hurt to keep NOT NULL redundantly in code repositories if the column is supposed to be NOT NULL. If you later decide to alter the PK constraint, you might forget to mark the column NOT NULL - or whether it even was supposed to be NOT NULL.

There is an item in the Postgres TODO wiki to decouple NOT NULL from the PK constraint. So this might change in future versions:

Move NOT NULL constraint information to pg_constraint

Currently NOT NULL constraints are stored in pg_attribute without any designation of their origins, e.g. primary keys. One manifestproblem is that dropping a PRIMARY KEY constraint does not remove theNOT NULL constraint designation. Another issue is that we shouldprobably force NOT NULL to be propagated from parent tables tochildren, just as CHECK constraints are. (But then does droppingPRIMARY KEY affect children?)

Answer to added question

Would it not be better if this self-contradictory CREATE TABLE justfailed right there?

As explained above, this

foo_id INTEGER NULL PRIMARY KEY

is (currently) 100 % equivalent to:

foo_id INTEGER PRIMARY KEY

Since NULL is treated as noise word in this context.
And we wouldn't want the latter to fail. So this is not an option.

If memory serves, the docs mention that:

• the null in create table statements is basically a noise word that gets ignored
• the primary key forces a not null and a unique constraint

See:

# create table test (id int null primary key);CREATE TABLE# \d test     Table "public.test" Column |  Type   | Modifiers --------+---------+----------- id     | integer | not nullIndexes:    "test_pkey" PRIMARY KEY, btree (id)

If as @ErwinBrandstetter said, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, you can use an UNIQUE constraint without NOT NULL instead of PRIMARY KEY. Example:

CREATE TABLE test(    id integer,    CONSTRAINT test_id_key UNIQUE(id));

This way you can do things like:

INSERT INTO test (id) VALUES (NULL);INSERT INTO test (id) VALUES (NULL);INSERT INTO test (id) VALUES (NULL);