>> -----Original Message-----
>> From: Alvaro Herrera [mailto:alvherre@dcc.uchile.cl]
>> Sent: Tuesday, July 01, 2003 5:51 PM
>> To: Jean-Christian Imbeault
>> Cc: techlist@voyager.phys.utk.edu; pgsql-general@postgresql.org
>> 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.