X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0700F9FB1FD for ; Thu, 27 Jul 2006 10:12:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 96754-03 for ; Thu, 27 Jul 2006 10:12:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 5DB419FA7B6 for ; Thu, 27 Jul 2006 10:12:23 -0300 (ADT) Received: (qmail 32616 invoked by uid 500); 27 Jul 2006 13:18:23 -0000 Date: Thu, 27 Jul 2006 08:18:23 -0500 From: Bruno Wolff III To: Michael Artz Cc: pgsql-sql@postgresql.org Subject: Re: Storing an ordered list Message-ID: <20060727131823.GA30179@wolff.to> Mail-Followup-To: Bruno Wolff III , Michael Artz , pgsql-sql@postgresql.org References: <20060726203954.GA23437@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.022 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200607/329 X-Sequence-Number: 25903 On Wed, Jul 26, 2006 at 20:13:03 -0400, Michael Artz wrote: > On 7/26/06, Bruno Wolff III wrote: > >If you use numeric instead of int, then it is easy to insert new values. > > Hmm, hadn't thought about that. How would you normally implement it? > I'm thinking that, if I wanted to insert between A and B, I could take > (A.order + B.order)/2, which would be pretty simple. Is there a > better way? I think that will depend. To keep the size of the number down, you will probably want to use the number with the fewest digits to the right of the decimal point that gives you a number between the two values. That will be a bit more complicated than the above formula. But you will want to do something to keep the size of the numerics down since it seems like reordering will be common. Another issue to consider is concurrency. You may want to lock the table against concurrent reordering, as doing two at once may lead to some unexpected events.