Re: ERROR: no NOT NULL constraint found to drop

Michael Paquier <michael(at)paquier(dot)xyz> writes:
> On Mon, Apr 10, 2023 at 03:16:06PM +0800, Richard Guo wrote:
>> I run into error $subject with the query below:
>> # create table t1 (c int primary key null unique);
>> CREATE TABLE
>> # create table t2 (like t1);
>> CREATE TABLE
>> # alter table t2 alter c drop not null;
>> ERROR: no NOT NULL constraint found to drop

> Thanks for the report. This is not the only issue that has been
> pointed out with this patch, so it is going to be reverted if you look
> around here:

It's still good to know about it for next time. The issue I guess is
that LIKE with no options propagates column attnotnull bits, but not
constraints, so we now have an inconsistency: t2.c has attnotnull set
but there is nothing in pg_constraint to justify it. It seems to me
we're going to have to think about what we want to happen in this
case. In a green field we'd probably not propagate NOT NULL unless
told to copy constraints ... but is it okay to break functional
compatibility with the old behavior?

regards, tom lane