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@xxxxxxxxxxx
