Am Montag, 13. Dezember 2004 17:37 schrieb Bruno Wolff III: > On Mon, Dec 13, 2004 at 10:58:25 +0100, > > Janning Vygen <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings