> On Mar 24, 2024, at 09:32, Thiemo Kellner <thiemo@xxxxxxxxxxxxxxxxxxxx> 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.
