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