pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedIs this a buggy behavior?
26+ messages / 11 participants
[nested] [flat]
* Is this a buggy behavior?
@ 2024-03-24 14:25 sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 26+ messages in thread
From: sud @ 2024-03-24 14:25 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
@ 2024-03-24 14:54 ` Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:39 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 26+ messages in thread
From: Erik Wienhold @ 2024-03-24 14:54 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
@ 2024-03-24 14:59 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-24 14:59 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-03-24 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:35 ` Re: Is this a buggy behavior? sud <suds1434@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: Tom Lane @ 2024-03-24 15:17 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-03-24 15:28 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:36 ` Re: Is this a buggy behavior? Andreas Kretschmer <andreas@a-kretschmer.de>
2024-03-24 15:59 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 3 replies; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-24 15:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-03-24 15:36 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2024-03-24 15:41 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2 siblings, 1 reply; 26+ messages in thread
From: Andreas Kretschmer @ 2024-03-24 15:36 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:36 ` Re: Is this a buggy behavior? Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2024-03-24 15:41 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:44 ` Re: Is this a buggy behavior? Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 1 reply; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-24 15:41 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:36 ` Re: Is this a buggy behavior? Andreas Kretschmer <andreas@a-kretschmer.de>
2024-03-24 15:41 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-03-24 15:44 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
0 siblings, 1 reply; 26+ messages in thread
From: Andreas Kretschmer @ 2024-03-24 15:44 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:36 ` Re: Is this a buggy behavior? Andreas Kretschmer <andreas@a-kretschmer.de>
2024-03-24 15:41 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:44 ` Re: Is this a buggy behavior? Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2024-03-24 16:15 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
0 siblings, 0 replies; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-24 16:15 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-03-24 15:59 ` Erik Wienhold <ewie@ewie.name>
2 siblings, 0 replies; 26+ messages in thread
From: Erik Wienhold @ 2024-03-24 15:59 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
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:
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-03-24 16:08 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
2 siblings, 1 reply; 26+ messages in thread
From: Adrian Klaver @ 2024-03-24 16:08 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-03-24 16:15 ` Christophe Pettus <xof@thebuild.com>
2024-03-24 16:32 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
0 siblings, 1 reply; 26+ messages in thread
From: Christophe Pettus @ 2024-03-24 16:15 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
@ 2024-03-24 16:32 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:43 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
2024-03-25 06:59 ` Re: Is this a buggy behavior? Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-24 16:32 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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…
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
2024-03-24 16:32 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-03-24 16:43 ` Christophe Pettus <xof@thebuild.com>
2024-03-24 18:09 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
1 sibling, 1 reply; 26+ messages in thread
From: Christophe Pettus @ 2024-03-24 16:43 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
2024-03-24 16:32 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:43 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
@ 2024-03-24 18:09 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
0 siblings, 0 replies; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-24 18:09 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
2024-03-24 16:32 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-03-25 06:59 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-25 09:50 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
1 sibling, 1 reply; 26+ messages in thread
From: Laurenz Albe @ 2024-03-25 06:59 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
2024-03-24 16:32 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-25 06:59 ` Re: Is this a buggy behavior? Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-03-25 09:50 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-25 13:48 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
0 siblings, 1 reply; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-25 09:50 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
2024-03-24 16:32 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-25 06:59 ` Re: Is this a buggy behavior? Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-25 09:50 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-03-25 13:48 ` Christophe Pettus <xof@thebuild.com>
2024-03-25 15:38 ` Re: Is this a buggy behavior? Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Christophe Pettus @ 2024-03-25 13:48 UTC (permalink / raw)
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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 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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:08 ` Re: Is this a buggy behavior? Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
2024-03-24 16:32 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-25 06:59 ` Re: Is this a buggy behavior? Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-25 09:50 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-25 13:48 ` Re: Is this a buggy behavior? Christophe Pettus <xof@thebuild.com>
@ 2024-03-25 15:38 ` Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Ron Johnson @ 2024-03-25 15:38 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
On Mon, Mar 25, 2024 at 9:49 AM Christophe Pettus <xof@thebuild.com> wrote:
>
>
> > 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 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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-03-24 15:35 ` sud <suds1434@gmail.com>
2024-03-24 15:42 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 18:14 ` Re: Is this a buggy behavior? Peter J. Holzer <hjp-pgsql@hjp.at>
1 sibling, 2 replies; 26+ messages in thread
From: sud @ 2024-03-24 15:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thiemo Kellner <thiemo@gelassene-pferde.biz>; pgsql-general@lists.postgresql.org
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).*
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:35 ` Re: Is this a buggy behavior? sud <suds1434@gmail.com>
@ 2024-03-24 15:42 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
1 sibling, 0 replies; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-24 15:42 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:35 ` Re: Is this a buggy behavior? sud <suds1434@gmail.com>
@ 2024-03-24 18:14 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2024-03-24 18:23 ` Re: Is this a buggy behavior? David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Peter J. Holzer @ 2024-03-24 18:14 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:35 ` Re: Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 18:14 ` Re: Is this a buggy behavior? Peter J. Holzer <hjp-pgsql@hjp.at>
@ 2024-03-24 18:23 ` David G. Johnston <david.g.johnston@gmail.com>
2024-03-24 19:59 ` Re: Is this a buggy behavior? Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 1 reply; 26+ messages in thread
From: David G. Johnston @ 2024-03-24 18:23 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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. 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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Re: Is this a buggy behavior? Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:35 ` Re: Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 18:14 ` Re: Is this a buggy behavior? Peter J. Holzer <hjp-pgsql@hjp.at>
2024-03-24 18:23 ` Re: Is this a buggy behavior? David G. Johnston <david.g.johnston@gmail.com>
@ 2024-03-24 19:59 ` Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 0 replies; 26+ messages in thread
From: Peter J. Holzer @ 2024-03-24 19:59 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
@ 2024-03-24 15:39 ` Erik Wienhold <ewie@ewie.name>
2024-03-24 15:42 ` Re: Is this a buggy behavior? Thiemo Kellner <thiemo@gelassene-pferde.biz>
1 sibling, 1 reply; 26+ messages in thread
From: Erik Wienhold @ 2024-03-24 15:39 UTC (permalink / raw)
To: sud <suds1434@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Is this a buggy behavior?
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
2024-03-24 15:39 ` Re: Is this a buggy behavior? Erik Wienhold <ewie@ewie.name>
@ 2024-03-24 15:42 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
0 siblings, 0 replies; 26+ messages in thread
From: Thiemo Kellner @ 2024-03-24 15:42 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2024-03-25 15:38 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-24 14:25 Is this a buggy behavior? sud <suds1434@gmail.com>
2024-03-24 14:54 ` Erik Wienhold <ewie@ewie.name>
2024-03-24 14:59 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-24 15:28 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:36 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2024-03-24 15:41 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:44 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2024-03-24 16:15 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 15:59 ` Erik Wienhold <ewie@ewie.name>
2024-03-24 16:08 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-03-24 16:15 ` Christophe Pettus <xof@thebuild.com>
2024-03-24 16:32 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 16:43 ` Christophe Pettus <xof@thebuild.com>
2024-03-24 18:09 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-25 06:59 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-25 09:50 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-25 13:48 ` Christophe Pettus <xof@thebuild.com>
2024-03-25 15:38 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-24 15:35 ` sud <suds1434@gmail.com>
2024-03-24 15:42 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-03-24 18:14 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2024-03-24 18:23 ` David G. Johnston <david.g.johnston@gmail.com>
2024-03-24 19:59 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2024-03-24 15:39 ` Erik Wienhold <ewie@ewie.name>
2024-03-24 15:42 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
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