Re: ENUM type

On Wed, Jul 27, 2005 at 09:40:27AM -0700, Josh Berkus wrote:
> Chris,
>
> > The varchar primary key idea (which I think is probably the best
> > solution) is certainly normalized, but it is also certainly inefficient
> > disk-wise.
>
> Only if you're real short on RAM. Tiny lookup tables tend to get cached in
> the shared buffer cache and stay there. Your only real overhead is if the
> application has dozens of ENUMs in a query, causing the number of joins to
> exceed the number the plannner can plan well. Otherwise, you're preaching
> false optimization.

The issue isn't the lookup table; the issue is the space (and I/O) in
the main table.

> Overall, I'd say that this is really a waste of time compared to the kind of
> things you *could* be doing to make converting from MySQL easier, like
> updating and maintaining the database conversion scripts, writing substitutes
> for last_insert_id and replace into, or (best of all) writing a detailed
> "PostgreSQL for MySQL Users" guide. I personally have converted 3 production
> applications from MySQL to PostgreSQL, and encountered two total ENUM columns
> in the process.

Absolutely; somehow that got lost in the thread.

Are the database migration scripts not actively maintained?
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"