How to alter Postgres table data based on its contents?

Question

This is probably a super simple question, but I'm struggling to come up with the right keywords to find it on Google.

I have a Postgres table that has among its contents a column of type text named content_type. That stores what type of entry is stored in that row.

There are only about 5 different types, and I decided I want to change one of them to display as something else in my application (I had been directly displaying these).

It struck me that it's funny that my view is being dictated by my database model, and I decided I would convert the types being stored in my database as strings into integers, and enumerate the possible types in my application with constants that convert them into their display names. That way, if I ever got the urge to change any category names again, I could just change it with one alteration of a constant. I also have the hunch that storing integers might be somewhat more efficient than storing text in the database.

First, a quick threshold question of, is this a good idea? Any feedback or anything I missed?

Second, and my main question, what's the Postgres command I could enter to make an alteration like this? I'm thinking I could start by renaming the old content_type column to old_content_type and then creating a new integer column content_type. However, what command would look at a row's old_content_type and fill in the new content_type column based off of that?

Answer 1

If you're finding that you need to change the display values, then yes, it's probably a good idea not to store them in a database. Integers are also more efficient to store and search, but I really wouldn't worry about it unless you've got millions of rows.

You just need to run an update to populate your new column:

update table_name set content_type = (case when old_content_type = 'a' then 1 
  when old_content_type = 'b' then 2 else 3 end);

If you're on Postgres 8.4 then using an enum type instead of a plain integer might be a good idea.

Answer 2

Ideally you'd have these fields referring to a table containing the definitions of type. This should be via a foreign key constraint. This way you know that your database is clean and has no invalid values (i.e. referential integrity).

There are many ways to handle this:

1. Having a table for each field that can contain a number of values (i.e. like an enum) is the most obvious - but it breaks down when you have a table that requires many attributes.

2. You can use the Entity-attribute-value model, but beware that this is too easy to abuse and cause problems when things grow.

3. You can use, or refer to my implementation solution PET (Parameter Enumeration Tables). This is a half way house between between 1 & 2.