Обсуждение: Re: [GENERAL] Auto Ordering

then, why use int, isn't char better? (almost no need for batch).



>From: Herouth Maoz <herouth@oumail.openu.ac.il>
>To: "Shawn T. Walker" <shawn@netcrafters.com>
>CC: pgsql-general@postgreSQL.org
>Subject: Re: [GENERAL] Auto Ordering
>Date: Mon, 1 Nov 1999 17:57:13 +0200
>
>At 20:41 +0200 on 27/10/1999, Stuart Rison wrote:
>
>
> > In the example you give, you could do the changes with two UPDATE
> > commands:
> >
> > 1) UPDATE questions SET order=0 WHERE order=5;
> > 2) UPDATE questions SET order=order+1 WHERE order<5;
> >
> > It becomes more tricky when you try and move a question to a position
> > other than the first one (e.g. question #6 to move to position #3 and
>all
> > other questions to be shifted accordingly).
> >
> > This would take three UPDATEs:
> >
> > 1) UPDATE questions SET order=0 WHERE order=6;
> > 2) UPDATE questions SET order=order+1 WHERE order>=3 and order<6;
> > 3) UPDATE questions SET order=3 WHERE order=0;
>
>Here is an alternative method of thinking which I used in the past - it
>depends on other factors whether this is good or not. If only the order of
>the questions is important, and not the actual number, then you can use
>fractions. You can use a floating point field, or a fixed point (numeric)
>one, or just an int field that normally gets the numbers 100, 200, 300.
>
>Changing order then becomes very easy:
>
>   UPDATE questions SET the_order=50 WHERE the_order=600;
>
>Will change questions 100,200,300,400,500,600,700
>To 50,100,200,300,400,500,700.
>
From time to time, though, you will have to renumber your questions, to
>make sure you don't run out of fraction precision. You can do that with
>something like:
>
>SELECT the_order
>INTO TABLE temp_numbers
>FROM questions
>ORDER BY the_order;
>
>CREATE SEQUENCE new_seq INCREMENT 100 START 100;
>
>UPDATE questions
>SET the_order = nextval( 'new_seq' )
>WHERE questions.the_order = temp_numbers.the_order;
>
>DROP SEQUENCE new_seq;
>DROP TABLE temp_numbers;
>
>The idea is to do the renumbering in batch, and have a small penalty in
>"real time".
>
>Herouth
>
>--
>Herouth Maoz, Internet developer.
>Open University of Israel - Telem project
>http://telem.openu.ac.il/~herutma
>
>
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

At 21:00 +0200 on 01/11/1999, amy cheng wrote:


> then, why use int, isn't char better? (almost no need for batch).

It all depends in the application. First, one must note that a
variable-length char takes more space (More than four bytes), and also,
variable-length fields have a slight performance penalty in retrieval.

Second, the question is what you want to do with it. I once had an
application in which the data items were lines in a piece of text. One
could add a line anywhere. So, if you wanted to insert a line between line
number 340 and line number 420, it was easy to find a number in between -
the average of the two numbers (420+340)/2 = 380. This would give you some
space for later addition between line 340 and 380 - using the same formula
- or between 380 and 420.

I think doing such a calculation in text is a bit more awkward. But it all
depends on whether you need to allow inserts on a regular basis, and assign
the orders manually. It's a question of design.

BTW, I think my suggestion for batch renumbering won't work in all cases. I
think a better alternative would be:

SELECT the_order as old_order, 0 as new_order
INTO TABLE temp_numbers
FROM questions
ORDER BY old_order;

CREATE SEQUENCE new_seq INCREMENT 100 START 100;

UPDATE temp_numbers
SET new_order = nextval( 'new_seq' );

UPDATE questions
SET the_order = temp_numbers.new_order
WHERE questions.the_order = temp_numbers.old_order;

DROP SEQUENCE new_seq;
DROP TABLE temp_numbers;

I also think this can be done more easily in a pl/pgsql function, but I am
not exactly an expert on them.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma