You might have a "maintenance" query which could go through and renumber the sort order. In other words,
SELECT * FROM spieltage ORDER BY sort;
then for each row in the result, re-insert it with a new value for the sort order, increasing by integer values of 4, or whatever. This could be run "once-in-a-while" to help avoid precision problems, assuming that you will actually have enough updates to consider this an issue.
Note: You should probably copy the table into a temp table, delete from the original, then read the data from the temp while inserting into the original, then drop the temp table -- all of this within a single transaction, of course...
On Dec 13, 2004, at 2:08 PM, Bruno Wolff III wrote:
On Mon, Dec 13, 2004 at 19:37:41 +0100, Janning Vygen <[EMAIL PROTECTED]> wrote:
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.
For this emaxmple, I suggest considering using a numeric column for doing
the sorting. You can initial load it with integer values in a number of
ways. When you need to insert a new row with a value between two existing
rows you can use the fractional part of the sort value to give you an
apropiate value without having to modify existing rows.
It doesn't sound like you need to worry about renumbering after deletions,
since gaps shouldn't cause a problem in the sort order. For the actual
reports, the application can number the records consecutively as they
are returned rather than displaying the sort column values.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
----------------------------------------------------------- Frank D. Engel, Jr. <[EMAIL PROTECTED]>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life.
$
___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
