Re: table with sort_key without gaps

Am Montag, 13. Dezember 2004 17:37 schrieb Bruno Wolff III:
> On Mon, Dec 13, 2004 at 10:58:25 +0100,
>
>   Janning Vygen <vygen@gmx.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.

ok, i have users which wants to manage their sporting competitions which
(simplified) has games and fixtures (in german "Spieltage", i hope the word
fixtures is understandable). Like German "Bundesliga" has 9 games on
"Spieltag 1", 7 on saturday and two on sunday.

So i have a table:

CREATE TABLE spieltage (
  account  text NOT NULL,
  sort int4 NOT NULL,
  name text NOT NULL
  PRIMARY KEY (account, sort),
  UNIQUE (account, name)
)

and another table (which is not interesting here) with games having a foreign
key referencing spieltage(account, sort). Of course every "spieltag" has a
unique name but needs more important a sort column.

I need to have sort as a primary key or at least a unique key (which is nearly
the same) because many other tables should reference the (primary or
candidate) key (account, sort) for the main reason that i can easily sort
other tables according to the sort column without the need to make a join.

updating/inserting/deleting to the table spieltage takes happen very seldom,
but it should be possible.

When i have three rows and i want to insert one row between sort "1" and sort
"2" i have to move all columns by one.

sample data when using one sequence for sort column

account | sort
--------------
acc1    | 1
acc1    | 2
acc2    | 3
acc2    | 4
acc1    | 5


now i insert VALUES ('acc1', 2) i need to move all existing rows out of the
way.

ah, as i am writing i understand my problem:

i CAN say:

SELECT nextval('spieltage_sort_seq'); -- i might move a column to currval
UPDATE spieltage SET sort = -(sort + 1) WHERE account = 'acc1' and sort >= 2;
UPDATE spieltage SET sort = -(sort)     WHERE account = 'acc1'  and sort < 0;
INSERT INTO spieltage VALUES ('acc1', 3);

right?

because the duplicate sort column value '3' after moving isnt a problem
because of the two-column primary key which only enforces uniquness of
(account, sort)

the other reason why i wanted gapless sequences was that i would love to use
the id in an URL. But this is easy to manage to translate a positional id in
an URL to the database id.

ok. I think i am going to use sequences. But after all i am wondering to find
so little stuff for this common problem. Lots of people have tables which
have a sort column (example: top ten lists) but i guess normally the sort
column is NOT the primary key.

kind regards
janning