Re: Duplicate key insert question

Finding if the duplicate value exists and inserting if not.

As for the race condition ( your other post )
I do not know how that will work for PG, but in Microsoft SQL Server
you can do following
BEGIN TRANSACTION
UPDATE [val] = [val]
WHERE ....
INSERT ...
COMMIT TRANSACTION

Pretty general approach tho, should work on any SQL system with
transaction and locking support.

so basically by updating specific row ( let say you have such row )
in transaction, row/page lock will be held until end of transaction
and concurrent UPDATE will wait until you are done.
Kind of semaphore.

Practical example table that holds unique rows, let say documents,
you can have extra row with let say [id] = -1 or whatever you like,
so during insert into that table you can update that row in a
transaction,
search/insert unique values, commit transaction.

-----Original Message-----
From: Jean-Christian Imbeault [mailto:jc(at)mega-bucks(dot)co(dot)jp]
Sent: Tuesday, July 01, 2003 5:47 PM
To: Maksim Likharev
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Duplicate key insert question

Maksim Likharev wrote:
>
> Do search using IF EXISTS SELECT ... or LEFT OUTER JOIN ... WHERE ...
IS
> NULL.
> works pretty fast.

Sorry, I don't understand. Works pretty fast for what?

Is that a way of finding if a value exists? or a way of doing the
insertion?

Thanks,

Jean-Christian Imbeault