If I run a select from a column in PostgreSQL that is of type enum, are the string values of the enum joined in before or after the final result set has been created? An explanation of my motive can be found at http://archives.postgresql.org/pgsql-performance/2008-07/msg00226.php
+1
A:
Internally it uses the OID the whole time. But you can cast that to text at any time.
Scott Bailey
2010-03-30 15:36:53
So this means that a cast to text will make the database look up the value in the system table?
David
2010-03-30 21:11:57
I think you are trying to out think the database. Its usually better to just let it do what it does instead of wasting time trying to pre-optimize based on what you think it will do. But any time you use an emum it looks up the oid and label in the pg_enum table. But then it caches that so later when you want to convert to text, it doesn't have to go back to the table again.
Scott Bailey
2010-03-30 21:49:14