Re: Is this a buggy behavior?

[Ron Johnson]

On Mon, Mar 25, 2024 at 9:49 AM Christophe Pettus  wrote:

>
>
> > On Mar 25, 2024, at 02:50, Thiemo Kellner 
> wrote:
> > My bad. I was under the impression that the create table statement was
> an atomic process/transaction with all its bells and whistles for
> constraints and keys, instead of a succession of alter statements.
>
> That may be a bit judgmental. :-)  It's not a series of ALTER statements,
> really.  The basic issue is that the parser throws away a bare NULL very
> early in the process, so it is not available to consult at the point that
> PostgreSQL is creating the constraint.  The underlying implementation of
> the actual table creation isn't the issue here.
>
> There seems to be general consensus that:
>
> 1. It would be handy to have a warning in the particular case that NULL is
> specified, however,
> 2. It would be undesirable to have a warning in the case where no NULL at
> all is specified, which means,
> 3. The presence of an existing bare NULL would have to be retained through
> the whole process, which is,
> 4. Not trivial.
>
> The reason the SQL standard is relevant here is that if bare NULL were
> part of the standard, that would be an argument for taking the pains.
> Since it's not, it's not clear that doing the work to add the warning is
> worth the effort.
>

Such a warning *could* be put in psql*, but is the effort worth the
benefit?  I don't really think OP's scenario is very common.

*People using pgAdmin, pgcli, etc wouldn't see the warning.

Re: Is this a buggy behavior?

[Christophe Pettus]

> On Mar 25, 2024, at 02:50, Thiemo Kellner  wrote:
> My bad. I was under the impression that the create table statement was an 
> atomic process/transaction with all its bells and whistles for constraints 
> and keys, instead of a succession of alter statements.

That may be a bit judgmental. :-)  It's not a series of ALTER statements, 
really.  The basic issue is that the parser throws away a bare NULL very early 
in the process, so it is not available to consult at the point that PostgreSQL 
is creating the constraint.  The underlying implementation of the actual table 
creation isn't the issue here.

There seems to be general consensus that:

1. It would be handy to have a warning in the particular case that NULL is 
specified, however,
2. It would be undesirable to have a warning in the case where no NULL at all 
is specified, which means,
3. The presence of an existing bare NULL would have to be retained through the 
whole process, which is,
4. Not trivial.

The reason the SQL standard is relevant here is that if bare NULL were part of 
the standard, that would be an argument for taking the pains.  Since it's not, 
it's not clear that doing the work to add the warning is worth the effort.

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 25.03.2024 um 07:59 schrieb Laurenz Albe:

On Sun, 2024-03-24 at 17:32 +0100, Thiemo Kellner wrote:

How can that be forgotten? This information ends up in the data
catalogue eventually!


It *is* stored in the catalog.

But if you add a primary key, that is tantamount to saying

   ALTER TABLE tab ALTER col SET NOT NULL;

>
> So it overrides the definition you made before.
>

My bad. I was under the impression that the create table statement was 
an atomic process/transaction with all its bells and whistles for 
constraints and keys, instead of a succession of alter statements.



Or would you want the above statement to cause an error just
because somebody had defined the column nullable before?


When getting contradictory information, I just would ask back what 
really was meant. But as a succession of alter statements, there is no 
contradiction. In that case, no.


Kind regards and thanks for the insights.

Thiemo

Re: Is this a buggy behavior?

[Laurenz Albe]

On Sun, 2024-03-24 at 17:32 +0100, Thiemo Kellner wrote:
> > The reason it doesn't give you a warning is that by the time it would
> > be in a position to, it's forgotten that you explicitly said NULL.
> 
> How can that be forgotten? This information ends up in the data 
> catalogue eventually!

It *is* stored in the catalog.

But if you add a primary key, that is tantamount to saying

  ALTER TABLE tab ALTER col SET NOT NULL;

So it overrides the definition you made before.

Or would you want the above statement to cause an error just
because somebody had defined the column nullable before?

Perhaps you'd want that, but you are one of the few who do.
You'll have to get used to the way it is.

Yours,
Laurenz Albe

Re: Is this a buggy behavior?

[Peter J. Holzer]

On 2024-03-24 11:23:22 -0700, David G. Johnston wrote:
> On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer  wrote:
> It doesn't. Your statement
> 
> > CREATE TABLE test1
> > (
> > c1 numeric   NULL ,
> > c2 varchar(36)  NOT NULL ,
> > CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> > ) ;
> 
> creates the table with both columns being defined as NOT NULL:
> 
> 
> 
> The request is a warning when defining a multi-column table constraint primary
> key if any of the columns comprising said PK are not defined already to be NOT
> NULL.
> 
> Personally, green field at least, I find that to be reasonable.

Frankly, I don't. I see no reason why I should have declare a column
in a PK explicitely as NOT NULL. 

Something like

CREATE TABLE test1
(
c1 numeric,
c2 varchar(36),
CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
) ;

or

create table test2 (
id serial primary key,
...
);

should be totally fine. An explicit NOT NULL here is just noise and
doesn't add value.

I have some sympathy for the idea that an explicit NULL in a column
definition should cause a warning if the resulting column would not in
fact be nullable. But since writing NULL is otherwise exactly equivalent
to writing nothing, even that seems a bit inconsistent and might be
more confusing than helpful. In any case it seems like a very low-value
change to me which should only be done if it's very little effort
(which apparently it isn't).

hp


-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Is this a buggy behavior?

[David G. Johnston]

On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer  wrote:

>
> It doesn't. Your statement
>
> > CREATE TABLE test1
> > (
> > c1 numeric   NULL ,
> > c2 varchar(36)  NOT NULL ,
> > CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> > ) ;
>
> creates the table with both columns being defined as NOT NULL:
>
>
The request is a warning when defining a multi-column table
constraint primary key if any of the columns comprising said PK are not
defined already to be NOT NULL.

Personally, green field at least, I find that to be reasonable.  Especially
if we are altering catalog metadata to define the columns to be not null,
as opposed to say the case when a check constraint has a "col is not null"
condition that could never pass even though the column itself is null-able.

David J.

Re: Is this a buggy behavior?

[Peter J. Holzer]

On 2024-03-24 21:05:04 +0530, sud 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),

This is a strange hope.

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

It doesn't. Your statement

> CREATE TABLE test1
> (
> c1 numeric   NULL ,
> c2 varchar(36)  NOT NULL ,
> CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> ) ;

creates the table with both columns being defined as NOT NULL:

hjp=> CREATE TABLE test1
(
c1 numeric   NULL ,
c2 varchar(36)  NOT NULL ,
CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
) ;
CREATE TABLE
Time: 16.815 ms
hjp=> \d test1
 Table "hjp.test1"
╔╤═══╤═══╤══╤═╗
║ Column │ Type  │ Collation │ Nullable │ Default ║
╟┼───┼───┼──┼─╢
║ c1 │ numeric   │   │ not null │ ║
║ c2 │ character varying(36) │   │ not null │ ║
╚╧═══╧═══╧══╧═╝
Indexes:
"test1_pk" PRIMARY KEY, btree (c1, c2)


> And then , while inserting the null record, it should say that the PK
> constraint is violated but not the "not null constraint" violated.

That may just be an artifact of the implementation. You can check
whether a value to be inserted is null or not without searching the
table, so that is done first. Only then you have to check the index for
a possible duplicate value, so that's done later.

But as a user I actually prefer it that way. The more precisely the
database can tell me why the insert failed, the better.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 17:43 schrieb Christophe Pettus:

The situation is much more like the customer saying, "I understand that the standard 
paint for this car is red, but I wish it painted blue instead."


Not in the least. Declaring the column to be NULL is explicitly 
requesting the car be blue. And declaring, in the same statement, there 
be a pk on that column is implicitly requesting the car be red.



Again, you can argue that PostgreSQL should remember that you explicitly asked 
for a NULL and generate a warning in that case, but that's not a trivial amount 
of work, since right now, that NULL is thrown away very early in statement 
processing.


Only, if PG is not aware of being in the process of creating a table.

Well, I do not feel, I can make myself understood.

Re: Is this a buggy behavior?

[Christophe Pettus]

> On Mar 24, 2024, at 09:32, Thiemo Kellner  wrote:
> Am 24.03.2024 um 17:15 schrieb Christophe Pettus:
>> I think the point is that it's not really doing anything "silently."  You 
>> are asking for a PRIMARY KEY constraint on a column, and it's giving it to 
>> you.  One of the effects (not even really a side-effect) of that request is 
>> that the column is then declared NOT NULL.
> 
> But don't you also request the database to have the column being nullable? 
> So, PG, at this point silently prioritises the request for the PK over the 
> request of the nullability. Does it not?

No.  The NULL is noise and is discarded.  PostgreSQL instantly forgets that you 
explicitly said NULL.  The difference between:

CREATE TABLE t (i int NULL); -- and
CREATE TABLE t (i int);

... doesn't make it to the point that the constraint is actually created.

>> The reason it doesn't give you a warning is that by the time it would be in 
>> a position to, it's forgotten that you explicitly said NULL.
> 
> How can that be forgotten? This information ends up in the data catalogue 
> eventually!

See above.  The fact that the column can contains nulls is retained, but that 
you explicitly said NULL is not.

> I would agree if you had two separate statements there, but in the example it 
> were not two different statements but one single contradictory statement.

The answer to all of these is the same: NULL is noise.  It has no more effect 
than if you said:

CREATE TABLE t (i int); -- Remember, "i" can contain NULLs!

> The issue however arose, because the statement said. "Please order me a blue 
> car painted in red." Hopefully, any single salesman should respond with 
> something like. "Dear customer, all very well, but it contradictory to have a 
> blue car painted in red. Do you want a red car or a blue one?"

The situation is much more like the customer saying, "I understand that the 
standard paint for this car is red, but I wish it painted blue instead."

Again, you can argue that PostgreSQL should remember that you explicitly asked 
for a NULL and generate a warning in that case, but that's not a trivial amount 
of work, since right now, that NULL is thrown away very early in statement 
processing.

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 17:15 schrieb Christophe Pettus:

I think the point is that it's not really doing anything "silently."  You are 
asking for a PRIMARY KEY constraint on a column, and it's giving it to you.  One of the 
effects (not even really a side-effect) of that request is that the column is then 
declared NOT NULL.


But don't you also request the database to have the column being 
nullable? So, PG, at this point silently prioritises the request for the 
PK over the request of the nullability. Does it not?




The reason it doesn't give you a warning is that by the time it would be in a 
position to, it's forgotten that you explicitly said NULL.


How can that be forgotten? This information ends up in the data 
catalogue eventually!



It does see that the column in nullable, but that in itself isn't worth 
emitting a warning over, since you are explicitly telling it that now the 
column shouldn't be null.


I would agree if you had two separate statements there, but in the 
example it were not two different statements but one single 
contradictory statement.



It wouldn't make much more sense to emit a warning there than it would be in 
this situation:

CREATE TABLE t (i int NULL);
ALTER TABLE t ALTER i SET NOT NULL;


Again, these are two separate statements.

Maybe an example can help.

You are describing the situation when one goes to a car salesman and 
orders a car painted in blue. The car gets manufactured and the salesman 
hands you over the key. Then you say to the salesman. Now, please, 
re-paint it in red.


The issue however arose, because the statement said. "Please order me a 
blue car painted in red." Hopefully, any single salesman should respond 
with something like. "Dear customer, all very well, but it contradictory 
to have a blue car painted in red. Do you want a red car or a blue one?"



Dunkel war's, der Mond schien helle,
Als ein Wagen blitze schnelle,
langsam um die runde Ecke fuhr…

Re: Is this a buggy behavior?

[Christophe Pettus]

On 3/24/24 08:28, Thiemo Kellner wrote:
> Sure, my example has lots more side effect than silently do the right thing.

I think the point is that it's not really doing anything "silently."  You are 
asking for a PRIMARY KEY constraint on a column, and it's giving it to you.  
One of the effects (not even really a side-effect) of that request is that the 
column is then declared NOT NULL.

The reason it doesn't give you a warning is that by the time it would be in a 
position to, it's forgotten that you explicitly said NULL.  It does see that 
the column in nullable, but that in itself isn't worth emitting a warning over, 
since you are explicitly telling it that now the column shouldn't be null.  It 
wouldn't make much more sense to emit a warning there than it would be in this 
situation:

CREATE TABLE t (i int NULL);
ALTER TABLE t ALTER i SET NOT NULL;

-- 
Adrian Klaver
adrian.kla...@aklaver.com

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:44 schrieb Andreas Kretschmer:

postgres=# create table bla(i int null primary key);
CREATE TABLE
postgres=# \d bla
     Table "public.bla"
  Column |  Type   | Collation | Nullable | Default
+-+---+--+-
  i  | integer |   | not null |
Indexes:
     "bla_pkey" PRIMARY KEY, btree (i)

postgres=# drop table bla;
DROP TABLE
postgres=# create table bla(i int not null primary key);
CREATE TABLE
postgres=# \d bla
     Table "public.bla"
  Column |  Type   | Collation | Nullable | Default
+-+---+--+-
  i  | integer |   | not null |
Indexes:
     "bla_pkey" PRIMARY KEY, btree (i)

postgres=#


as you can see, there is no difference.  the PK-Constraint is the 
important thing here.


This describes the END state perfectly. But while creating the table, 
that is the question.


I am thinking along the lines that a table is being created by "first" 
(1) the columns in their default state. That is, Nullable would be true. 
And after that (2), all the constraints get created. Because the not 
null constraint is not present in the column definition, there is no 
change. After that (3), the primary gets created, requiring an 
additional not null constraint. Assuming such a creation would lead to 
an error when one already exists, I suppose there is a check on the 
presence for the constraint.
If (2) and (3) is swapped, then in the step creating the not null 
constraint, one had to go through ALL the column definitions to retrieve 
on which one such a constraint is defined. At this point, one also could 
check whether the nullability of a column that has already been created 
is the one as defined, being explicitly using "null"/"not null" or the 
default.

Re: Is this a buggy behavior?

[Adrian Klaver]

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

Re: Is this a buggy behavior?

[Erik Wienhold]

On 2024-03-24 16:28 +0100, 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.
> 
> "Considering that this usage of NULL isn't even permitted by the SQL
> standard" is in my opinion a strange argument.

I don't know if the SQL standard ever allowed the NULL "constraint", but
the 2003 revision (the oldest one that I've got) does not allow it:

>From Part 2, 11.4 :

 ::=
NOT NULL
  | 
  | 
  | 

Postgres only accepts it to be compatible with other RDBMS. [1]

[1] 
https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-NULL

-- 
Erik

Re: Is this a buggy behavior?

[Andreas Kretschmer]

Am 24.03.24 um 16:41 schrieb Thiemo Kellner:



Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer:
the null-able constraint addition to a column is pointless because by 
default all columns are nullable. definition as a primary key adds 
the not null constraint.


While this is certainly true, I do not see why the information that a 
not null constraint is to be created or has been created is not 
available.





postgres=# create table bla(i int null primary key);
CREATE TABLE
postgres=# \d bla
    Table "public.bla"
 Column |  Type   | Collation | Nullable | Default
+-+---+--+-
 i  | integer |   | not null |
Indexes:
    "bla_pkey" PRIMARY KEY, btree (i)

postgres=# drop table bla;
DROP TABLE
postgres=# create table bla(i int not null primary key);
CREATE TABLE
postgres=# \d bla
    Table "public.bla"
 Column |  Type   | Collation | Nullable | Default
+-+---+--+-
 i  | integer |   | not null |
Indexes:
    "bla_pkey" PRIMARY KEY, btree (i)

postgres=#


as you can see, there is no difference.  the PK-Constraint is the 
important thing here.


Andreas



--
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:35 schrieb sud:
On Sun, Mar 24, 2024 at 8:47 PM Tom Lane > 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.

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:39 schrieb Erik Wienhold:

And that's also possible in Postgres with UNIQUE constraints if you're
looking for that behavior.


Sort of the distinction between PK and UQ.

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer:
the null-able constraint addition to a column is pointless because by 
default all columns are nullable. definition as a primary key adds the 
not null constraint.


While this is certainly true, I do not see why the information that a 
not null constraint is to be created or has been created is not available.

Re: Is this a buggy behavior?

[Erik Wienhold]

I wrote:
> Do you come from sqlite?  That allows NULL in primary key columns
> without an explicit NOT NULL constraint.

And that's also possible in Postgres with UNIQUE constraints if you're
looking for that behavior.

-- 
Erik

Re: Is this a buggy behavior?

[Andreas Kretschmer]

Am 24.03.24 um 16:28 schrieb Thiemo Kellner:


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?


the null-able constraint addition to a column is pointless because by 
default all columns are nullable. definition as a primary key adds the 
not null constraint.


Andreas

--
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support

Re: Is this a buggy behavior?

[sud]

On Sun, Mar 24, 2024 at 8:47 PM Tom Lane  wrote:

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

Re: Is this a buggy behavior?

[Thiemo Kellner]

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.


Please do not get me wrong. I can totally understand that something 
needs to much work to implement. I am just puzzled.

Re: Is this a buggy behavior?

[Tom Lane]

Thiemo Kellner  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.

regards, tom lane

Re: Is this a buggy behavior?

[Thiemo Kellner]

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.


The two dimes of Thiemo

Re: Is this a buggy behavior?

[Erik Wienhold]

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

Is this a buggy behavior?

[sud]

Hello All,
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


Regards
Sud