On Tue, Apr 6, 2021 at 1:03 PM Ron <ronljohnso...@gmail.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(), 'us...@example.com', 'manag...@example.com'),
(gen_random_uuid(), 'us...@example.com', 'manag...@example.com'),
(gen_random_uuid(), 'user3&example.com', 'manag...@example.com
'),
(gen_random_uuid(), 'us...@example.com', 'manager4.example.com
'),
(gen_random_uuid(), 'us...@example.com', 'manag...@example.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
