Weird error message from Postgres 18
Hi.
I noticed a weird message error from Postgres 18.
sql reproducible test:
create table inserttest (col1 serial PRIMARY KEY, col2 int4 NOT NULL, col3
text NOT NULL, col4 text NULL);
insert into inserttest select 0 AS col2, NULL AS col3, NULL AS col4;
ERROR: null value in column "col2" of relation "inserttest" violates
not-null constraint
DETAIL: Failing row contains (0, null, null, null).
drop table inserttest;
It seems that the message is confusing.
col2 and col3 IS NOT NULL, but col2 has value 0.
best regards,
Ranier Vilela
Attachments:
Ranier Vilela <ranier.vf@gmail.com> writes:
create table inserttest (col1 serial PRIMARY KEY, col2 int4 NOT NULL, col3
text NOT NULL, col4 text NULL);
insert into inserttest select 0 AS col2, NULL AS col3, NULL AS col4;
ERROR: null value in column "col2" of relation "inserttest" violates
not-null constraint
DETAIL: Failing row contains (0, null, null, null).
You are misunderstanding the effects of that INSERT. The AS labels
have zero to do with the semantics, so you are inserting 0 to col1,
null to col2 & col3, and by default null to col4.
regards, tom lane
Em qui., 21 de ago. de 2025 às 12:17, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
Ranier Vilela <ranier.vf@gmail.com> writes:
create table inserttest (col1 serial PRIMARY KEY, col2 int4 NOT NULL,
col3
text NOT NULL, col4 text NULL);
insert into inserttest select 0 AS col2, NULL AS col3, NULL AS col4;
ERROR: null value in column "col2" of relation "inserttest" violates
not-null constraint
DETAIL: Failing row contains (0, null, null, null).You are misunderstanding the effects of that INSERT. The AS labels
have zero to do with the semantics, so you are inserting 0 to col1,
null to col2 & col3, and by default null to col4.
Thanks for the clarification Tom.
best regards,
Ranier Vilela