Re: Check constraint failure messages

An error message that includes the table name and column name would also
be helpful for non-bulk inserts: a single insert where multiple columns
of a table have the same domain type. The problem gets worse when there
are inserts into multiple tables that have the same domain types
(multiple sql inserts in a statement).

Am 06.04.21 um 22:58 schrieb Ron:
> On 4/6/21 3:50 PM, Miles Elam wrote:
>> On Tue, Apr 6, 2021 at 1:03 PM Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
>>
>> On 4/6/21 2:40 PM, Miles Elam wrote:
>>> I've got a domain that validates email addresses. When inserting
>>> a bunch of entries I simply get the error message
>>>
>>> ERROR: value for domain po.email violates check constraint
>>> "email_check" SQL state: 23514
>>>
>>>
>>> When inserting 1000+ entries in a batch, finding the exact entry
>>> with the problem is noticeably harder than with other error
>>> types. For example when a column should be a uuid but you pass
>>> in 'Mary had a little lamb', the error message tells you what
>>> the invalid value is as well as the column name you're trying to
>>> put it into.
>>>
>>> Are there any quick hacks floating around out there to solve or
>>> at least mitigate this?
>>
>> Is it a deferred constraint?
>>
>> Plain ole domain CHECK constraint.
>>
>> CREATE DOMAIN po.email AS varchar
>>   CHECK (VALUE IS NULL OR (po.length_in(VALUE, 1, 254) AND NOT
>> po.email_expanded(VALUE) IS NULL));
>>
>>
>> where "po" is another schema, po.length_in(...) is an IMMUTABLE range
>> check, and po.email_expanded(...) is a function returning a record.
>> Same behavior happens if I remove the functions and define the check
>> constraint in place. The only info returned in a bulk insert is the
>> name of the violated check constraint, aka email_check.
>
> The blunt force answer is to not use bulk inserts.  Try COPY; it's
> good at saying which record throws an error.
>
> --
> Angular momentum makes the world go 'round.