Altering Postgres int columns to enum type

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/

Bookmark and Share

Tags:

About Rick van Hattem

Rick van Hattem is a Dutch Internet entrepreneur and co-founder of Fashiolista.com
No comments yet.

Leave a Reply