so basically I want to import a csv file and one of the columns has the data yes/no in it and I have created an enum that has values ‘red’ and ‘white’ and the yes related to ‘red’ in the enum and no relates to ‘white’ in the enum and the column in the table in the database is of the type enum I created. So, I have to change the yes to ‘red’ and no to ‘white’ in the table. I don’t even know what to search on google about this problem.
Advertisement
Answer
You can use a virtual column. This keeps the data type stored as a Boolean and uses the minimum of storage space.
You could create a column of VARCHAR(5) and populate it using an update after the importation if you prefer.
create table enums( importedBoolean boolean not null, enumColor varchar(5) GENERATED ALWAYS AS (case when importedBoolean then 'red' else 'white' end) STORED );✓
insert into enums values(True),(False);
2 rows affected
select * from enums;importedboolean | enumcolor :-------------- | :-------- t | red f | white
db<>fiddle here