Re: Autoincremental value - Mailing list pgsql-general

    Suppose your table is like :

    key1    key2
    1    1
    1    2
    2    1

    To get the next value to insert for key1=1 you can do this :

    SELECT key2 FROM ... WHERE key1=1 ORDER BY key2 DESC LIMIT 1

    Of course a UNIQUE INDEX on key1, key2 helps.

    You won't be protected from two transactions adding the same value at the
same time, though. The unique index will catch them and one of them will
fail (constraint violation etc). Just retry the transaction until it
works... or, be a warrior and lock the table... but if you do that, please
do it in a function/trigger so that it's not kept locked for long !