Many applications have need for a sort of “enum” field at some point. If you’re like me, you’ve probably used a sort of enum mapping in your code to map the numbers to the actual values so you don’t waste space by having duplicated text/varchar columns.
With PostgreSQL you can easily convert these to an enum type without having to repeat yourself too often (or, at all).
First, let’s create a new ENUM by selecting all distinct values (don’t forget to fill the table, column_name and enum_name):
SELECT 'CREATE TYPE <enum_name> AS ENUM (''' || (SELECT string_agg(DISTINCT <column>, ''', ''') FROM <table>) || ''');';
That should generate something along these lines which you can execute if it’s as you expect it:
CREATE TYPE <enum_name> AS ENUM ('spam', 'eggs', 'bacon');
Once you’ve created the type, you can convert the rows in the table to use the newly created enum instead of the varchar/text column:
ALTER TABLE <table>
ALTER COLUMN <column>
SET DATA TYPE <enum_name>
USING (enum_range(null::<enum_name>))[<column>::int + 1];
And we’re done. All converted to an ENUM type 🙂
Inspired by: https://www.munderwood.ca/index.php/2015/05/28/altering-postgresql-columns-from-one-enum-to-another/
