> On Mar 24, 2024, at 09:32, Thiemo Kellner <thi...@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 a column, and it's giving it to
>> you. One of the effects (not even really a side-effect) of that request is
>> that the column is 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 the request 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 explicitly said 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 one single 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 single salesman should respond with
> something like. "Dear customer, all very well, but it contradictory to have a
> blue car painted 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 wish it painted blue instead."
Again, you can argue that PostgreSQL should remember that you explicitly asked
for a NULL and generate a warning in that case, but that's not a trivial amount
of work, since right now, that NULL is thrown away very early in statement
processing.
