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?
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 |