Re: Is this a buggy behavior? - Mailing list pgsql-general

From Ron Johnson
Subject Re: Is this a buggy behavior?
Date
Msg-id CANzqJaBG5m2Mxi3E94tRusDEL1=FyLux3m568cCfGdwVaViVhA@mail.gmail.com
Whole thread Raw
In response to Re: Is this a buggy behavior?  (Christophe Pettus <xof@thebuild.com>)
List pgsql-general
On Mon, Mar 25, 2024 at 9:49 AM Christophe Pettus <xof@thebuild.com> wrote:


> On Mar 25, 2024, at 02:50, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
> My bad. I was under the impression that the create table statement was an atomic process/transaction with all its bells and whistles for constraints and keys, instead of a succession of alter statements.

That may be a bit judgmental. :-)  It's not a series of ALTER statements, really.  The basic issue is that the parser throws away a bare NULL very early in the process, so it is not available to consult at the point that PostgreSQL is creating the constraint.  The underlying implementation of the actual table creation isn't the issue here.

There seems to be general consensus that:

1. It would be handy to have a warning in the particular case that NULL is specified, however,
2. It would be undesirable to have a warning in the case where no NULL at all is specified, which means,
3. The presence of an existing bare NULL would have to be retained through the whole process, which is,
4. Not trivial.

The reason the SQL standard is relevant here is that if bare NULL were part of the standard, that would be an argument for taking the pains.  Since it's not, it's not clear that doing the work to add the warning is worth the effort.
 
Such a warning could be put in psql*, but is the effort worth the benefit?  I don't really think OP's scenario is very common.

*People using pgAdmin, pgcli, etc wouldn't see the warning.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Query on Postgres SQL transaction
Next
From: Justin Clift
Date:
Subject: Re: PostgreSQL as advanced job queuing system