Change ENUM Type for PostgreSQL

change_type.sql

-- Mochammad Dimas Editya
-- https://youtube.com/codersindonesia
--
-- Bukan orang GMF
-- Change ENUM Type in PostgreSQL
--
-- 1. Change enum name into another. So no one will use it
ALTER TYPE public."BudgetEnum" RENAME TO budgetenum;

-- 2. Create new enum type as you wish
CREATE TYPE public."BudgetEnum" AS ENUM ('OPEX', 'CAPEX', 'NA');

-- 3. Change column name that using budgetenum
ALTER TABLE request_project RENAME COLUMN source_of_budget TO source_of_budget_old;

-- 4. Add new column that will type from step 2 with default value (if necessary)
ALTER TABLE request_project ADD source_of_budget "BudgetEnum" NOT null DEFAULT 'NA';
 
-- 5. Select column that you need to be update on next process, and save it on virtual using "INTO"
SELECT 
	id, source_of_budget_old, source_of_budget 
INTO backup
FROM public.request_project;

-- 6. Using PLpgSQL, do some loop for doing update
DO 
$$
DECLARE
	backup_record RECORD;
	
BEGIN
	FOR backup_record IN SELECT * FROM backup
	LOOP
		UPDATE request_project 
		SET source_of_budget = backup_record.source_of_budget_old::text::"BudgetEnum"
		WHERE id = backup_record.id;
	END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 7. Delete all unnecessary type and columns
ALTER TABLE request_project DROP COLUMN source_of_budget_old;