Re: Can't find not null constraint, but \d+ shows that

On 2024-Apr-12, jian he wrote:

> Now I am more confused...

> +CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
> +ALTER TABLE notnull_tbl1 DROP c1;

> same query, mysql make let "c0" be not null

Yes, that was Postgres' old model. But the way we think of it now, is
that a column is marked attnotnull when a pg_constraint entry exists to
support that flag, which can be a not-null constraint, or a primary key
constraint. In the old Postgres model, you're right that we would
continue to have c0 as not-null, just like mysql. In the new model,
that flag no longer has no reason to be there, because the backing
primary key constraint has been removed, which is why we reset it.

So what I was saying in the cases with replica identity and generated
columns, is that there's an attnotnull flag we cannot remove, because of
either of those things, but we don't have any backing constraint for it,
which is an inconsistency with the view of the world that I described
above. I would like to manufacture one not-null constraint at that
point, or just abort the drop of the PK ... but I don't see how to do
either of those things.

If you want the c0 column to be still not-null after dropping the
primary key, you need to SET NOT NULL:

CREATE TABLE notnull_tbl1 (c0 int, c1 int, PRIMARY KEY (c0, c1));
ALTER TABLE notnull_tbl1 ALTER c0 SET NOT NULL;
ALTER TABLE notnull_tbl1 DROP c1;
\d+ notnull_tbl1
Table "public.notnull_tbl1"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description
────────┼─────────┼───────────┼──────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
c0 │ integer │ │ not null │ │ plain │ │ │
Not-null constraints:
"notnull_tbl1_c0_not_null" NOT NULL "c0"
Access method: heap

One thing that's not quite ideal, is that the "Nullable" column doesn't
make it obvious that the flag is going to be removed if you drop the PK;
you have to infer that that's going to happen by noticing that there's
no explicit not-null constraint listed for that column -- maybe too
subtle, especially if you have a lot of columns (luckily, PKs normally
don't have too many columns). This is why I suggested to change the
contents of that column if the flag is sustained by the PK. Something
like this, perhaps:

=# CREATE TABLE notnull_tbl1 (c0 int not null, c1 int, PRIMARY KEY (c0, c1));
=# \d+ notnull_tbl1
Table "public.notnull_tbl1"
Column │ Type │ Collation │ Nullable │ Default │ Storage │ Compression │ Stats target │ Description
────────┼─────────┼───────────┼─────────────┼─────────┼─────────┼─────────────┼──────────────┼─────────────
c0 │ integer │ │ not null │ │ plain │ │ │
c1 │ integer │ │ primary key │ │ plain │ │ │
Indexes:
"notnull_tbl1_pkey" PRIMARY KEY, btree (c0, c1)
Not-null constraints:
"notnull_tbl1_c0_not_null" NOT NULL "c0"
Access method: heap

which should make it obvious.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended." (Gerry Pourwelle)