Re: Auto Increase

Marcelo Pereira wrote:
>
> Hi Mike,
>
> Ok, I agree. But, take a look:
>
> => insert into patr_local (local_cod,local_descr) values (3,'local A');
> INSERT
> => insert into patr_local (local_descr) values ('local B');
> INSERT
> => insert into patr_local (local_descr) values ('local C');
> INSERT
> => insert into patr_local (local_descr) values ('local D');
> ERROR: Duplicated key
> => insert into patr_local (local_descr) values ('local D');
>
> select * from patr_local
> 3 - local A
> 1 - local B
> 2 - local C
> 4 - local D
>
> As you can see, the tupple 'local D' was unable to be inserted because it
> could have '3' as the value of local_cod, but '3' has already been used.
>
> How can I solve it? It would have to add once more to get an empty
> value, and once, and once, an once, until it gets an legal value.

You can either do that with a function, written in PL/PgSQL for example
(see the manual), but then the search for the next value can take an
arbitrary time, depending on how big the biggest value is.

Another variant would be to set the sequence to the value after a
successful insertion to local_cod, but I guess this has some problems
when multiple users are trying to do an insert.

Yet another method would be to ignore the value passed and always use
the next_val from the sequence, but then there's no elegant method to
see which value local_cod will have at the next insertion, which makes
it difficult to write client code that needs this value when it is used
for another table to refer to the new record for example.

In short, the easiest solution is probably to always use next_val
instead of a DEFAULT.