> On Mar 24, 2024, at 09:32, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
> Am 24.03.2024 um 17:15 schrieb Christophe Pettus:
>> I think the point is that it's not really doing anything "silently." You are asking for a PRIMARY KEY constraint on
acolumn, and it's giving it to you. One of the effects (not even really a side-effect) of that request is that the
columnis then declared NOT NULL.
>
> But don't you also request the database to have the column being nullable? So, PG, at this point silently prioritises
therequest for the PK over the request of the nullability. Does it not?
No. The NULL is noise and is discarded. PostgreSQL instantly forgets that you explicitly said NULL. The difference
between:
CREATE TABLE t (i int NULL); -- and
CREATE TABLE t (i int);
... doesn't make it to the point that the constraint is actually created.
>> The reason it doesn't give you a warning is that by the time it would be in a position to, it's forgotten that you
explicitlysaid NULL.
>
> How can that be forgotten? This information ends up in the data catalogue eventually!
See above. The fact that the column can contains nulls is retained, but that you explicitly said NULL is not.
> I would agree if you had two separate statements there, but in the example it were not two different statements but
onesingle contradictory statement.
The answer to all of these is the same: NULL is noise. It has no more effect than if you said:
CREATE TABLE t (i int); -- Remember, "i" can contain NULLs!
> The issue however arose, because the statement said. "Please order me a blue car painted in red." Hopefully, any
singlesalesman should respond with something like. "Dear customer, all very well, but it contradictory to have a blue
carpainted in red. Do you want a red car or a blue one?"
The situation is much more like the customer saying, "I understand that the standard paint for this car is red, but I
wishit painted blue instead."
Again, you can argue that PostgreSQL should remember that you explicitly asked for a NULL and generate a warning in
thatcase, but that's not a trivial amount of work, since right now, that NULL is thrown away very early in statement
processing.