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.

On Sun, Mar 24, 2024 at 8:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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

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]https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-NULL

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

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

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.

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!"