Re: Check constraint failure messages

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.

An example table using it is defined as follows

CREATE TABLE IF NOT EXISTS profile (
id uuid PRIMARY KEY,
email po.email NOT NULL,

manager_email po.email NOT NULL

);

Nothing fancy.

INSERT INTO profile (id, email, manager_email) VALUES
(gen_random_uuid(), 'user1(at)example(dot)com', 'manager1(at)example(dot)com'),
(gen_random_uuid(), 'user2(at)example(dot)com', 'manager2(at)example(dot)com'),

(gen_random_uuid(), 'user3&example.com', 'manager3(at)example(dot)com
'),
(gen_random_uuid(), 'user4(at)example(dot)com', 'manager4.example.com
'),
(gen_random_uuid(), 'user5(at)example(dot)com', 'manager5(at)example(dot)com
');

Inserting this batch will tell me that there was an error and that it was
"email_check" that failed, but no indication that the 3rd user's email
address or the 4th user's manager email was the problem, forcing a bisect
operation among 1,000+ entries to find the first error, then bisect from
there to find the second error if any, and repeat until no more constraint
errors.

- Miles