stan <stanb@panix.com> writes:
> On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:
>> On 9/15/19 10:46 AM, stan wrote:
>>> So, my test tell me that the validity check is done BEFORE an attempt to
>>> insert (thus firing the trigger) occurs.
>> What validity check?
> The check to see if it is the type enum.
Indeed, a trigger cannot fix an input-validity error, because that
will happen while trying to form the row value that would be passed
to the trigger. So I guess that when you say "the trigger doesn't
fire" you really mean "this other error is raised first".
However, I still don't understand your claim that it works the
way you wanted in an INSERT statement. The enum input function
is going to complain in either context.
Generally you need to fix issues like this before trying to
insert the data into your table. You might try preprocessing
the data file before feeding it to COPY. Another way is to
copy into a temporary table that has very lax column data types
(all "text", perhaps) and then transform the data using
INSERT ... SELECT from the temp table to the final storage table.
regards, tom lane
