From: | Erik Wienhold <ewie(at)ewie(dot)name> |
---|---|
To: | sud <suds1434(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Is this a buggy behavior? |
Date: | 2024-03-24 14:54:31 |
Message-ID: | yv3w7fykxqfqyio2mwentwipoojror5ebwhthpw4xfoiwaxikj@jri3ds4u7xfr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2024-03-24 15:25 +0100, sud wrote:
> Create a table and composite primary key. But to my surprise it allowed me
> to have the composite primary key created even if one of the columns was
> defined as nullable. But then inserting the NULL into that column erroring
> out at the first record itself , stating "not null constraint" is violated.
>
> CREATE TABLE test1
> (
> c1 varchar(36) NULL ,
> c2 varchar(36) NOT NULL ,
> CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> ) ;
>
> -- Table created without any error even one of the columns in the PK was
> defined as NULL.
>
> insert into test1 values(null,'123');
>
>
> *ERROR: null value in column "c1" of relation "test1" violates not-null
> constraintDETAIL: Failing row contains (null, 123).*
>
> insert into test1 values('123','123');
>
> --works fine as expected
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.
Do you come from sqlite? That allows NULL in primary key columns
without an explicit NOT NULL constraint.
--
Erik
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2024-03-24 14:59:26 | Re: Not able to purge partition |
Previous Message | Lok P | 2024-03-24 14:42:04 | Re: Not able to purge partition |