Thread: Thanks for all the help on replacing 'unique'
I just wanted to issue a blanket "thanks" to all who responded to my inquiry about what to do about 'unique'. Distinct is the equivalent aggregate function as many pointed out. A co-worker said that Oracles 'unique' is probably an optimized version of distinct that takes advantage of some Oracle specific indexing. The example that I used to illustrate my problem in the first place was seriously flawed as many pointed out. The PostgreSQL db had been created from a Fastreader control file that had all column names in double quotes, ex. "total" instead of total; This produced a normal looking DB that did not react correctly to any SQL that used a column name. So for example, select count(*) would return the correct row count but count(some_column) would produce a 'no such column error'. My difficulty with unique was mostly caused by the failure of every attempt to come up with an alternate query which was caused, in turn, by the columns names used to create the tables. All in all, a pretty revolting experience with one possible bright spot, I think I have proved a little known principle of computer science: "garbage in, garbage out". Many thanks to all who took the time to reply, -=bill
Wm.A.Stafford wrote: > I just wanted to issue a blanket "thanks" to all who responded to my > inquiry about what to do about 'unique'. Distinct is the equivalent > aggregate function as many pointed out. A co-worker said that Oracles > 'unique' is probably an optimized version of distinct that takes > advantage of some Oracle specific indexing. > > The example that I used to illustrate my problem in the first place was > seriously flawed as many pointed out. The PostgreSQL db had been > created from a Fastreader control file that had all column names in > double quotes, ex. "total" instead of total; This produced a normal > looking DB that did not react correctly to any SQL that used a column > name. So for example, select count(*) would return the correct row > count but count(some_column) would produce a 'no such column error'. > > My difficulty with unique was mostly caused by the failure of every > attempt to come up with an alternate query which was caused, in turn, > by the columns names used to create the tables. > > All in all, a pretty revolting experience with one possible bright > spot, I think I have proved a little known principle of computer > science: "garbage in, garbage out". I have updated an FAQ item to specifically mention capitalization, so that should help people in the future. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On 21.11.2006 15:20 Wm.A.Stafford wrote: > Distinct is the equivalent > aggregate function as many pointed out. A co-worker said that Oracles > 'unique' is probably an optimized version of distinct that takes > advantage of some Oracle specific indexing. I don't think so. They are merely two different keywords for the same thing. I seen more than one DB using UNIQUE instead of DISTINCT. Quote from the Oracle manual: "these two keywords are synonymous" http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065648 Thomas