On Thu, Jan 24, 2013 at 12:03 PM, Steve Clark <sclark@xxxxxxxxxxxxx> wrote: > On 01/24/2013 01:06 PM, Chris Angelico wrote: >> >> On Fri, Jan 25, 2013 at 4:45 AM, Steve Clark <sclark@xxxxxxxxxxxxx> wrote: >>> >>> Thanks All, >>> >>> This is for a few very small tables, less 100 records each, that a user >>> can >>> delete and insert records into based on the "id" >>> which is displayed in a php generated html screen. The tables are rarely >>> updated and when they are updated only one person >>> is accessing them at a time. >>> >>> I have seen several answers on inserting what about deleting? >> >> Deleting works exactly the same way; you just subtract instead of adding. >> >> And thanks Jeff, I forgot about that requirement. Still, searched >> update is the easiest solution. >> >> However, do seriously rethink your design. At very least, the "id" >> field is misnamed; it's not the record's identity if it changes. If >> your only two operations are "insert" and "delete" (with inserts >> permitted at either end of the list as well as in the middle), one way >> you could do it is to have a serially-numbered ID, and a 'pos'. Adding >> to the end means inserting a row with a pos one higher than the >> current highest. Inserting a record before another one means inserting >> a row with the same pos - no renumbering needed. Deleting a row is >> done by its id, not its position. And when you query the table, just >> ask for them "ORDER BY POS, ID DESC" - this will show them in the >> right order. This doesn't, however, handle arbitrary reordering of >> records. For that, you will ultimately need to renumber the positions. >> >> ChrisA >> >> > Hi Chris, > > > It is really called rule_num and relates to "in what order firewall rules > are applied". And it used > to allow the user to place the firewall rules where they want them in > relation to other rules. > > This is an old design, of which I had no input, but am now maintaining. Like > I said initially I have > php, bash or C code to do the reordering and was just wondering if there was > a slick way to > do it without having to resort to some external mechanism. > > Thanks to all who responded. So do the numbers need to be a gapless sequence? if not why not have each position be, say, 10,000 apart, and just insert new ones halfway between the two nearest rules? -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
