Re: Duplicate key insert question

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