Skip site navigation (1) Skip section navigation (2)

Re: ENUM type

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-advocacy(at)postgresql(dot)org
Cc: Chris Travers <chris(at)travelamericas(dot)com>,Jeff Davis <jdavis-pgsql(at)empires(dot)org>
Subject: Re: ENUM type
Date: 2005-07-27 16:40:27
Message-ID: 200507270940.27510.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-hackers
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.

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.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2005-07-27 17:36:23
Subject: Re: [HACKERS] Enticing interns to PostgreSQL
Previous:From: Andrew DunstanDate: 2005-07-27 15:30:10
Subject: Re: ENUM type

pgsql-advocacy by date

Next:From: Josh BerkusDate: 2005-07-27 16:45:27
Subject: Re: SQL/PSM for 8.2 will offer ANSI/ISO, MySQL, and DB2 Compatibility
Previous:From: Andrew DunstanDate: 2005-07-27 15:30:10
Subject: Re: ENUM type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group