Re: table with sort_key without gaps

On Mon, Dec 13, 2004 at 10:58:25 +0100,
Janning Vygen <vygen(at)gmx(dot)de> wrote:
> Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
>
> maybe your are right. But with Sequences i thought to have problems when i do
> inserts in the middle of the sorting array. I need to move all current rows
> out of the way to insert a new one. Insert a row at id 3 i need to do
>
> UPDATE mytable SET id = -(id + 1) WHERE id >= 3;
> UPDATE mytable SET id = -(id) WHERE id < 0;
> INSERT INTO mytable VALUES (3);
>
> -- UPDATE mytable SET id = id + 1 WHERE id >= 3;
> -- doesnt work in pgsql if id is a primary key
>
> but with sequences i just have to push my sequence counter up, too. Right?

Sequences should really only be used to obtain unique values. It is dangerous
to assume any other semantics other than that within a session the values
returned by nextval TO THAT SESSION will monotonically increase.

> SELECT nextval('mytable_id_seq');
>
> ok, it should work with sequences, too. I will try it. but isn't there a ready
> to use model which explains and avoids problems like the one with the update
> statement above?

You still haven't told us why you want to remove the gaps in the id.
Unless you have some business reason for doing that, you shouldn't be
doing that. If you told us what the business reason for doing that is,
then we may be able to give you some better suggestions.