Re: not null constraints, again

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Cc: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Tender Wang <tndrwang(at)gmail(dot)com>
Subject: Re: not null constraints, again
Date: 2024-11-07 05:59:34
Message-ID: CACJufxGeq2xVq_FN21=iLK5uKnbnH4o_B3FEofP1H_xuzkAtMQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

sql-altertable.html

<varlistentry id="sql-altertable-desc-set-drop-not-null">
<term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
<listitem>
<para>
These forms change whether a column is marked to allow null
values or to reject null values.
</para>
<para>
If this table is a partition, one cannot perform <literal>DROP
NOT NULL</literal>
on a column if it is marked <literal>NOT NULL</literal> in the parent
table. To drop the <literal>NOT NULL</literal> constraint from all the
partitions, perform <literal>DROP NOT NULL</literal> on the parent
table.
</para>
Now this will be slightly inaccurate.

drop table if exists part, part0 cascade;
create table part (a int not null) partition by range (a);
create table part0 (a int not null);
alter table part attach partition part0 for values from (0) to (1000);
alter table ONLY part0 add primary key(a);
alter table part alter column a drop not null;

as the example shows that part0 not-null constraint is still there.
that means:

perform <literal>DROP NOT NULL</literal> on the parent table
will not drop the <literal>NOT NULL</literal> constraint from all partitions.

so we need rephrase the following sentence:

To drop the <literal>NOT NULL</literal> constraint from all the
partitions, perform <literal>DROP NOT NULL</literal> on the parent
table.

to address this kind of corner case?

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2024-11-07 06:07:36 Re: Disallow UPDATE/DELETE on table with unpublished generated column as REPLICA IDENTITY
Previous Message Andrei Lepikhov 2024-11-07 05:57:46 Re: Incremental Sort Cost Estimation Instability