pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Is this a buggy behavior?
Date: Sun, 24 Mar 2024 09:08:18 -0700
Message-ID: <582a40a4-ac92-4247-8cea-bae9f2da5c73@aklaver.com> (raw)
In-Reply-To: <eba267a8-7945-4754-bee5-6113cac477fc@gelassene-pferde.biz>
References: <CAD=mzVV6SKgj0-vO2+vx2x3qHXDw6ZCSCkm3EnAKpwkOPjUgrA@mail.gmail.com>
	<yv3w7fykxqfqyio2mwentwipoojror5ebwhthpw4xfoiwaxikj@jri3ds4u7xfr>
	<51eb3063-1d90-40c6-a8c6-9a77fe105a61@gelassene-pferde.biz>
	<2005111.1711293431@sss.pgh.pa.us>
	<eba267a8-7945-4754-bee5-6113cac477fc@gelassene-pferde.biz>

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@aklaver.com






view thread (26+ messages)  latest in thread

Message-ID: <582a40a4-ac92-4247-8cea-bae9f2da5c73@aklaver.com>
Permalink:  ../582a40a4-ac92-4247-8cea-bae9f2da5c73@aklaver.com/
Also on:    postgresql.org/message-id/582a40a4-ac92-4247-8cea-bae9f2da5c73@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, thiemo@gelassene-pferde.biz, tgl@sss.pgh.pa.us
  Subject: Re: Is this a buggy behavior?
  In-Reply-To: <582a40a4-ac92-4247-8cea-bae9f2da5c73@aklaver.com>
  References: <CAD=mzVV6SKgj0-vO2+vx2x3qHXDw6ZCSCkm3EnAKpwkOPjUgrA@mail.gmail.com> <yv3w7fykxqfqyio2mwentwipoojror5ebwhthpw4xfoiwaxikj@jri3ds4u7xfr> <51eb3063-1d90-40c6-a8c6-9a77fe105a61@gelassene-pferde.biz> <2005111.1711293431@sss.pgh.pa.us> <eba267a8-7945-4754-bee5-6113cac477fc@gelassene-pferde.biz> <582a40a4-ac92-4247-8cea-bae9f2da5c73@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox