Re: Equivalent for AUTOINCREMENT?

On Sat, Nov 1, 2008 at 12:33 PM, Michelle Konzack
<linux4michelle(at)tamay-dogan(dot)net> wrote:
> Hello Michael,
>
> Am 2008-10-31 11:15:54, schrieb Michael Hall:
>> > I have a table where I have a serialnumber which shuld be increased be
>> > each INSERT. I know I can use max() to get the highest number, but how
>> > can I use it in a INSERT statement?
>>
>> Have a look in the manual for the SERIAL data type.
>> For fields with a SERIAL data type, you can use DEFAULT in an INSERT statement.
>
> OK, the SERIAL is now working, but there is ONE problem.
>
> If the INSERT fails, the SERIAL is in any case increased...
>
> So this was the reason, why I have tried to use max(serno) and add +1
> the get the next value for "serno".
>
> Is there a solution for it?

Note that this is not only how pgsql and oracle and most other big
databases work. It's also how innodb tables in mysql work:

http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html

and for good reason, that as mentioned elsewhere, gapless sequences
tend to scale poorly.

>
> Since I can use a transaction block if required, there should be no
> problem with concurence access.
>
> Thanks, Greetings and nice Day/Evening
> Michelle Konzack
> Systemadministrator
> 24V Electronic Engineer
> Tamay Dogan Network
> Debian GNU/Linux Consultant
>
>
> --
> Linux-User #280138 with the Linux Counter, http://counter.li.org/
> ##################### Debian GNU/Linux Consultant #####################
> Michelle Konzack Apt. 917 ICQ #328449886
> +49/177/9351947 50, rue de Soultz MSN LinuxMichi
> +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)
>

--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis