Re: table with sort_key without gaps

On Thu, Dec 09, 2004 at 18:32:19 +0100,
Janning Vygen <vygen(at)gmx(dot)de> wrote:
>
> "id" should be positive
> "id" should not have gaps within the same account
> "id" should start counting by 1 for each account
>
> i cant use sequences because they are producing gaps and doesn't start
> counting by 1 for each account and i dont want to use postgresql array type
> for various reasons.
>
> for this model to function you need a lot of sophisticated plpgsql function to
> insert, move or delete entries to keep

I doubt you want to use this model if you are going to be deleting records.

> - did anyone implemented a table like this and wrote some custom
> functions/triggers for inserting, deleting, moving and so on? If yes it would
> be nice if he/she is willing to sahre the code with me.

If you aren't deleting records and you don't have a lot of concurrent requests,
you can lock the table and select the current max id for an account and add
1 to get the next id for for that account.

> - did anyone implemented a table like this and came to the conclusion that
> this shouldn't be done for any reasons out of my sight? (i don't bother about
> updating a primary key)

Why are you doing this? Normally uniqness of an ID is good enough. If you
don't need to worry about gaps, you could use one sequence for the entire
table to generate IDs.