Re: table with sort_key without gaps - Mailing list pgsql-general

Yeah, that suggestion sounds good as long as you ensure that the sort
column has sufficient precision to handle the in-between values.  I
would suggest checking for value-above and value-below when inserting,
then using their midpoint.  In the event that there is no value-above,
add some integer number to the last used value, preferably > 1 (maybe
4, for example), to help avoid the possibility of running out of
precision.

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 <vygen@gmx.de> 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 majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>
-----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ 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