Thread: NOT NULL violation and error-message
Hi all, when trying to insert/update a NOT NULL column with a null-values (in this case the "created"-column), we get
thiserror:
ERROR: null value in column "created" violates not-null constraint
Using JDBC this error-message is what appears in the SQLException.getMessage() which makes it impossible to tell which
tablethe NOT_NULL_VIOLATION happened in. This can be a real pain as this might happen upon transaction-commit using
OR-toolslike Hibernate. The transaction might involve updating several tables with the same column-name rendering it
impossibleto extract what the error really is from the error-message. Is there a way to prefix the column-name in the
error-messagewith table-name and maybe also schema-name? The message would then instead read something like:
ERROR: null value in column "public"."mytable"."created" violates not-null constraint
Oracle does this btw...
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. | |
|
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
Andreas Joseph Krogh <andreak@officenet.no> wrote:
> ERROR: null value in column "created" violates not-null constraint
It is easy to add the table name to the message, but ...
> ERROR: null value in column "public"."mytable"."created" violates not-null constraint
> Oracle does this btw...
Do we have any guideline about the message for identifier names? We've
already had serveral "table.column" messages, but "schema.table.column"
might be preferred if there are tables with the same name in different
schema. In addition, separated quotes ("schema"."table"."column") are
more SQL-ish than single outer quotes. Which should we use?
At any rate, we need to adjust many regression test and .po files
if we change such kinds of messages.
Index: src/backend/executor/execMain.c
===================================================================
--- src/backend/executor/execMain.c (HEAD)
+++ src/backend/executor/execMain.c (fixed)
@@ -1316,7 +1316,8 @@ slot_attisnull(slot, attrChk)) ereport(ERROR,
(errcode(ERRCODE_NOT_NULL_VIOLATION),
- errmsg("null value in column \"%s\" violates not-null constraint",
+ errmsg("null value in column \"%s.%s\" violates not-null constraint",
+ RelationGetRelationName(rel), NameStr(rel->rd_att->attrs[attrChk -
1]->attname)))); } }
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
On Tuesday 12. January 2010 11.10.09 Takahiro Itagaki wrote:
>
> Andreas Joseph Krogh <andreak@officenet.no> wrote:
>
> > ERROR: null value in column "created" violates not-null constraint
>
> It is easy to add the table name to the message, but ...
>
> > ERROR: null value in column "public"."mytable"."created" violates not-null constraint
> > Oracle does this btw...
>
> Do we have any guideline about the message for identifier names? We've
> already had serveral "table.column" messages, but "schema.table.column"
> might be preferred if there are tables with the same name in different
> schema. In addition, separated quotes ("schema"."table"."column") are
> more SQL-ish than single outer quotes. Which should we use?
>
> At any rate, we need to adjust many regression test and .po files
> if we change such kinds of messages.
>
>
> Index: src/backend/executor/execMain.c
> ===================================================================
> --- src/backend/executor/execMain.c (HEAD)
> +++ src/backend/executor/execMain.c (fixed)
> @@ -1316,7 +1316,8 @@
> slot_attisnull(slot, attrChk))
> ereport(ERROR,
> (errcode(ERRCODE_NOT_NULL_VIOLATION),
> - errmsg("null value in column \"%s\" violates not-null constraint",
> + errmsg("null value in column \"%s.%s\" violates not-null constraint",
> + RelationGetRelationName(rel),
> NameStr(rel->rd_att->attrs[attrChk - 1]->attname))));
> }
> }
+1
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. | |
|
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> writes:
> Do we have any guideline about the message for identifier names?
The issue has come up before. I think that what we really need here is
to implement additional fields in error message reports, so that the
name and schema name of the relevant object can be pulled out of the
message by programs without having to parse the message text. If we had
that, it would reduce the pressure to clutter the basic message text
this way. It would also improve our compliance with the SQL standard,
which says you're supposed to do it like that.
regards, tom lane
On Tue, Jan 12, 2010 at 5:10 AM, Takahiro Itagaki
<itagaki.takahiro@oss.ntt.co.jp> wrote:
> Andreas Joseph Krogh <andreak@officenet.no> wrote:
>
>> ERROR: null value in column "created" violates not-null constraint
>
> It is easy to add the table name to the message, but ...
>
>> ERROR: null value in column "public"."mytable"."created" violates not-null constraint
>> Oracle does this btw...
>
> Do we have any guideline about the message for identifier names? We've
> already had serveral "table.column" messages, but "schema.table.column"
> might be preferred if there are tables with the same name in different
> schema. In addition, separated quotes ("schema"."table"."column") are
> more SQL-ish than single outer quotes. Which should we use?
I feel like we ought to be doing this in a way where the output is
properly escaped. Right now:
test=# create table dork ("""" integer not null);
CREATE TABLE
test=# insert into dork values (null);
ERROR: null value in column """ violates not-null constraint
Suck.
...Robert
Robert Haas <robertmhaas@gmail.com> wrote: > I feel like we ought to be doing this in a way where the output is > properly escaped. Right now: Yeah, that's been an occasional irritation for me, even though I don't actually use quote characters in any of my quoted identifiers. Also, tab-completion is irritating in not recognizing matching column names when you start with a quote if the column name is entirely lower case. I would complain about generating a mix of quoted and non-quoted column names in some places, but fixing that for me would undoubtedly inconvenience others; I guess we have to consider that behavior a feature. But the other two just look like sloppy coding to me; it's hard to see any excuse not to call them bugs, albeit minor ones. -Kevin
Robert Haas <robertmhaas@gmail.com> writes:
> I feel like we ought to be doing this in a way where the output is
> properly escaped.
This is one of several reasons why code shouldn't be trying to scrape
the names out of the human-readable message.
regards, tom lane