On 9/17/19 2:31 AM, stan wrote:
>>> I am not certain what you mean by the check. As you can see, there is nor
>>> specific check clause. I was referring to the built in check of data being
>>> entered versus the legal values for the user defined type. Make sense?
>>>
>>
>> To be clear you are seeing an error because a value of say 'active' is being
>> rejected before your trigger has a chance to upper case it, correct?
>>
>> Also this happens whether you use \copy or manually INSERT the values?
>
> That is correct. Sorry this was not clear from the beginning.
>
> Any suggestions, including changing the design here, are welcome.
Suggestions:
1) Per Tom's post clean the data before it hits the database.
2) Enter the data directly into the database using something that forces
the user to enter only the correct ENUM values.
3) If the status field is only ever going to be ACTIVE/INACTIVE change
it to a BOOLEAN field active_status and be done with the ENUM dance.
4) If status may ever be more then ACTIVE/ACTIVE then change it to
varchar and use the trigger to set case(if still important) and/or
verify correct entries.
--
Adrian Klaver
adrian.klaver@aklaver.com