Re: NOT NULL NOT ENFORCED

On 2026-Feb-23, jian he wrote:

> On Thu, Sep 25, 2025 at 5:46 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
> > >
> > > CREATE TABLE tx3 (x int not null not enforced);
> > >
> > > can be dumped as:
> > >
> > > CREATE TABLE public.tx3 (x integer);
> > > ALTER TABLE public.tx3 ADD CONSTRAINT tx3_x_not_null NOT NULL x NOT ENFORCED;
> > > ---------------
> > > note: currently not enforced check constraint is dumped separately.
> >
> > Hmm, I wonder what's the reason for this.  Seems quite useless.  Why
> > wouldn't we dump unenforced constraint together with the table?  The
> > case is different from invalid constraints, which have to be created
> > after data is loaded.

> However, if we intend to emit NOT ENFORCED together with other column
> attributes during dump, we must distinguish between NOT VALID ENFORCED and
> NOT VALID NOT ENFORCED.

But what is NOT VALID NOT ENFORCED?  Unless I'm confused, a constraint
can be in one of three cases,

1. the normal one (valid and enforced), which is dumped together with
   the table,
2. the NOT ENFORCED one, for which there's no validity marking (these
   are all in practice invalid, but we don't spell that out because it's
   pointless to do so), which I argue should also be dumped together
   with the table,
3. NOT VALID but enforced, which is dumped separately, because trying to
   dump it together with the table just won't work.


I understand that you want to treat 2 the same as 3, because it saves a
dozen of pg_dump lines of code.  I think it's more user friendly to
treat 2 the same as 1, and that the dozen lines of code are worth it.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/