Re: Is this a buggy behavior?

On 3/24/24 08:28, Thiemo Kellner wrote:
> 
> Am 24.03.2024 um 16:17 schrieb Tom Lane:
> 
>> To do that, we'd have to remember that you'd said NULL, which we
>> don't: the word is just discarded as a noise clause.  Considering
>> that this usage of NULL isn't even permitted by the SQL standard,
>> that seems like a bit too much work.
> 
> If I understood correctly, only the NOT NULL expression gets remembered, 
> but the NULL gets discarded. No, I do not quite get it. Somehow, it has 
> to be decided whether to create a "check constraint" or not, but this 
> information is not available any more when creating the primary key? Not 
> even in some kind of intermediary catalogue?
> 
> "Considering that this usage of NULL isn't even permitted by the SQL 
> standard" is in my opinion a strange argument. To me, it is similar as 
> to say, well a column has a not null constraint and that must be enough, 
> we do not check whether the data complies when inserting or updating. 
> Sure, my example has lots more side effect than silently do the right 
> thing.

That is sort of the point the OPs example was for a CREATE TABLE and 
hence had no data. The OP also wanted a PK and per:

https://www.postgresql.org/docs/current/sql-createtable.html

"PRIMARY KEY enforces the same data constraints as a combination of 
UNIQUE and NOT NULL. "

they got a compound PK with the specified constraints.

If they had being doing a ALTER TABLE to add a PK over the columns after 
null values where added they result would be different:

CREATE TABLE test1
(
c1 varchar(36)   NULL ,
c2 varchar(36)  NOT NULL
) ;

insert into test1 values (null, 'test');

alter table test1 add constraint test_pk PRIMARY KEY(c1,c2);
ERROR:  column "c1" of relation "test1" contains null values

> 
> Please do not get me wrong. I can totally understand that something 
> needs to much work to implement. I am just puzzled.
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com