Thread: Is this a buggy behavior?

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

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

Thiemo Kellner <thiemo@gelassene-pferde.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.

            regards, tom lane

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.

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

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

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.

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.

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

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

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 <column definition>:

    <column constraint> ::=
        NOT NULL
      | <unique specification>
      | <references specification>
      | <check constraint definition>

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

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

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
columnis 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
explicitlysaid NULL.  It does see that the column in nullable, but that in itself isn't worth emitting a warning over,
sinceyou are explicitly telling it that now the column shouldn't be null.  It wouldn't make much more sense to emit a
warningthere than it would be in this situation: 

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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.

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
acolumn, and it's giving it to you.  One of the effects (not even really a side-effect) of that request is that the
columnis 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
explicitlysaid 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
explicitlytelling 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…

> On Mar 24, 2024, at 09:32, Thiemo Kellner <thiemo@gelassene-pferde.biz> 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
acolumn, and it's giving it to you.  One of the effects (not even really a side-effect) of that request is that the
columnis 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
therequest 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
explicitlysaid 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
onesingle 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
singlesalesman should respond with something like. "Dear customer, all very well, but it contradictory to have a blue
carpainted 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
wishit painted blue instead." 

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

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
wishit 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
thatcase, 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.

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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2024-03-24 11:23:22 -0700, David G. Johnston wrote:
> On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer <hjp-pgsql@hjp.at> 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.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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

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

> On Mar 25, 2024, at 02:50, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
> My bad. I was under the impression that the create table statement was an atomic process/transaction with all its
bellsand 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
throwsaway a bare NULL very early in the process, so it is not available to consult at the point that PostgreSQL is
creatingthe 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
fortaking the pains.  Since it's not, it's not clear that doing the work to add the warning is worth the effort.