Re: Check constraint failure messages

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