Thread: Reuse serial numbers?
Hello. I have a table create table TBL (a serial, b integer, c integer, unique(b,c)); so I for each combination of b,c get an unique number a. Now, if the table is filled and I remove some rows, so there is a gap in the a numbers. Is there a way to reuse the "free" serial numbers? I don't feel like writing a script to select all numbers and do some searching for free numbers.
am Thu, dem 27.03.2008, um 9:36:57 +0000 mailte A B folgendes: > Hello. > I have a table > create table TBL (a serial, b integer, c integer, unique(b,c)); > > so I for each combination of b,c get an unique number a. > > Now, if the table is filled and I remove some rows, so there is a gap > in the a numbers. Is there a way to reuse the "free" serial numbers? > > I don't feel like writing a script to select all numbers and do some > searching for free numbers. Why do you think you need the numbers? Normally, you don't need this, a sequence can produce sufficient numbers. If you really need this behavior: http://www.varlena.com/GeneralBits/130.php Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thu, Mar 27, 2008 at 10:49:06AM +0100, A. Kretschmer wrote: > > Why do you think you need the numbers? A typical application for low numbers (not letting them increase over time) is the "human readabilitly" if you need them outside the DB as labels on boxes for "real life transactions". One solution is writing a function using a self join on the column on a.n + 1 = b.n searching for NULL results; then returning the a.n+1. I have this somewhere in the jungle of my source code snippets. Tell me if you need me searching for it :) Emil
Oh, I just thought of having a boolean field for indicating usage...I don't know if it will be more efficient. But as a novice... I think that will be sufficient. 2008/3/27, Emil Obermayr <nobs@nobswolf.info>: > On Thu, Mar 27, 2008 at 10:49:06AM +0100, A. Kretschmer wrote: > > > > Why do you think you need the numbers? > > > A typical application for low numbers (not letting them increase over > time) is the "human readabilitly" if you need them outside the DB as > labels on boxes for "real life transactions". > > One solution is writing a function using a self join on the column > on a.n + 1 = b.n searching for NULL results; then returning the a.n+1. > > I have this somewhere in the jungle of my source code snippets. Tell me > if you need me searching for it :) > > > Emil > > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice >
On Thu, Mar 27, 2008 at 10:17:51AM +0000, A B wrote: > Oh, I just thought of having a boolean field for indicating usage...I > don't know if it will be more efficient. But as a novice... I think > that will be sufficient. Efficiency is alway a point of course. But you have to keep track of your field and you create a redundant data structure. You might run into inconsistancy with it. "My" solution is self-consistant, not redundant and you don't need write operations during seeking for the fre entry. So maybe its even more efficient.