>> -----Original Message----- >> From: Alvaro Herrera [mailto:[EMAIL PROTECTED] >> Sent: Tuesday, July 01, 2003 5:51 PM >> To: Jean-Christian Imbeault >> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] >> Subject: Re: [GENERAL] Duplicate key insert question >> >> >> On Wed, Jul 02, 2003 at 09:45:23AM +0900, Jean-Christian >> Imbeault wrote: >> Reuben D. Budiardja wrote: [snip] >> If I followed all the arguments correctly according to the thread >> there is *no* way to do what I (and you ;) want in one simple query. > > No, there's not. You should check the returned value from the insertion > function to see if it succeeded or not. Sadly, an error will cause the > whole transaction to abort, but if they come from the MySQL side it will > hardly matter. But you should try to use a sequence if at all possible > to avoid all these problems.
Does not really avoid the named issue. Suppose that you have a dictionary of working part numbers (e.g. Boeing might have 3 million distinct parts in their database). They would like to create a domain for these parts. So, naturally, they take their list and do %cat list.dat|sort|uniq>list.sor And then bulk load list.sor. Unfortunately, the operation fails, because one part was duplicated: PartID PartDescription -------- --------------------------------- 94v-975b High speed saphire needle bearing 94V-975B High speed saphire needle bearing It would have been nice if after loading 1.7 million of the 3 million parts, it could simply skip over the obvious error instead of rolling everything back. Of course, it is also possible that 94v-975b and 94V-975B are distinct parts. So the one who designs the database must make that decision in allowing an IGNORE option. I think it would be a useful addition to PostgreSQL, but I have an easy work around for what I want to do by simply capitalizing the strings I am inserting into a dictionary or domain and use select distinct to filter. The rare times I want to do something like that incrementally, I can just request a table lock. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
