Re: Make sure there is no two column with same value - What is the best practice?

On 12/02/10 08:17, Yan Cheng Cheok wrote:
> Hello, currently, I would like to make sure :
>
> (1) When insert a new item, there is no 2nd row with same column value.
> (2) If there is, I will just directly take the existing row.
>
> I was thinking out of 2 approach. May I know which one is the common used best practice?
>
> // Shall I make the **entire** procedure atomic and synchronized.
> // This means, every time, there is only
> // one thread can execute it. But, does PostgreSQL provide such features?
> //
> stored_procedure
> {
> if (column.value is not "Mickey Mouse") {
> insert new row with one of the column.value is "Mickey Mouse"
> }
> return row id with its column.value is "Mickey Mouse"
> }

You could lock the whole table, but that will kill performance.

> stored_procedure
> {
> // I make this column.value to be unique
> if (column.value is not "Mickey Mouse") {
> try {
> insert new row with one of the column.value is "Mickey Mouse"
> }
> catch (Is Not Unique Exception) {
> }
> }
> return row id with its column.value is "Mickey Mouse"
> }

This would be a more typical approach, perhaps. You will want to loop
checking for the row and trying to insert. It is possible another
connection might insert then delete "Mickey Mouse" while this procedure
is executing.

--
Richard Huxton
Archonet Ltd