Re: Is this a buggy behavior?

On Sun, Mar 24, 2024 at 8:47 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz> writes:
> > Am 24.03.2024 um 15:54 schrieb Erik Wienhold:
> >> This is required by the SQL standard: columns of a primary key must be
> >> NOT NULL. Postgres automatically adds the missing NOT NULL constraints
> >> when defining a primary key. You can verify that with \d test1 in psql.
>
> > To me, this behaviour, while correct, is not too concise. I wished, that
> > PG issued a warning about a definition conflict. In PostgreSQL, a PK
> > must always be not nullable, so explicitly defining on of a PK's columns
> > as nullable is contradictory, one should get notified of.
>
> 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.
>

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.

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).*