On Tue, Apr 6, 2021 at 1:03 PM Ron <ronljohnsonjr@xxxxxxxxx> 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 varcharCHECK (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@xxxxxxxxxxx', 'manager1@xxxxxxxxxxx'),(gen_random_uuid(), 'user2@xxxxxxxxxxx', 'manager2@xxxxxxxxxxx'),
(gen_random_uuid(), 'user3&example.com', 'manager3@xxxxxxxxxxx'),
(gen_random_uuid(), 'user4@xxxxxxxxxxx', 'manager4.example.com'),
(gen_random_uuid(), 'user5@xxxxxxxxxxx', 'manager5@xxxxxxxxxxx');
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
