Re: Is this a buggy behavior?

Am 24.03.2024 um 16:35 schrieb sud:
> On Sun, Mar 24, 2024 at 8:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
> Do you specifically mean that 'null'  keyword is just not making any
> sense here in postgres. But even if that is the case , i tried inserting
> nothing (hoping "nothing" is "null" in true sense), but then too it
> failed in the first statement while inserting which is fine as per the PK.

To the best of my knowledge, your assumption is correct. And therefore
the insert must fail because a pk never must contain null values.

> But don't you think,in the first place it shouldn't have been allowed to
> create the table with one of the composite PK columns being defined as
> NULL. And then , while inserting the null record, it should say that the
> PK constraint is violated but not the "not null constraint" violated.
>
> CREATE TABLE test1
> (
> c1 numeric   NULL ,
> c2 varchar(36)  NOT NULL ,
> CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> ) ;
>
> insert into test1(c2) values('123');
>
> /*ERROR: null value in column "c1" of relation "test1" violates not-null
> constraint DETAIL: Failing row contains (null, 123).*/

I feel largely the same way. The definition is contradictory but there
is no message to tell you so.