Skip to content
Advertisement

How do I change a text to an enum type?

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement