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(dot)klaver(at)aklaver(dot)com