On Fri, 2005-10-28 at 16:28 -0500, Jim C. Nasby wrote: > On Fri, Oct 28, 2005 at 04:36:26PM -0400, Rod Taylor wrote: > > On Fri, 2005-10-28 at 15:21 -0500, Jim C. Nasby wrote: > > > On Fri, Oct 28, 2005 at 02:57:03PM -0400, Rod Taylor wrote: > > > > The basic idea is that most of us break out schemas by creating fake > > > > primary keys for the purpose of obtaining performance because using the > > > > proper primary key (single or multiple columns) is often very slow. > > > > > > > > The automatic and transparent creation of a surrogate key by PostgreSQL > > > > would allow us to dramatically clean up the presentation of our schema > > > > to the users using the database without the performance hit we currently > > > > get. > > > > > > > > > > > > It puts surrogate keys (fake primary keys) back to the level of table > > > > spaces, indexes and other performance enhancements where they belong. > > > > > > Ahh. Yes, that would definately be great to have. Although it would > > > probably take me months if not years to get used to not seeing a bunch > > > of _id fields laying all over the place... > > > > > > Is SURROGATE part of any of the ANSI specs? > > > > No, but neither is an index, rollback segment, or table space. The ANSI > > spec doesn't usually deal with performance tweaks that are the > > responsibility of the DBA. > > True, but none of those other things you mention affect external > representation of data. But I was more wondering if we were inventing > syntax on the fly here or not...
It isn't supposed to impact the external representation of the data and generally neither is an ENUM outside of the potential sorting ability. I was just getting the impression that the big push for enums was to be able to use a 'real word' but without a performance hit. A regular old table, foreign key to a varchar gives you the 'real word' and the surrogate key allows you to do so without a performance hit. -- ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly