On Wed, Dec 14, 2005 at 16:22:54 -0600,
Bruno Wolff III <bruno(at)wolff(dot)to> wrote:
> On Wed, Dec 14, 2005 at 16:23:14 +0200,
> Roland Giesler <roland(at)giesler(dot)za(dot)net> wrote:
> > Is it possible to create a sequence like 'ABC012','ABC013', etc. in a
> > char(6) column?
> > I'm toying with some ideas...
> > I actually need to generate random 6 digit membership number in the format
> > 'CCCNNN' (where C is a character A-Z and D a digit 0-9). The idea is to
> > have a number like a South African motorvehicle registration (also used
> > elsewhere) in the format XYZ123. For security reasons a member number like
> > this should be random and I'm wondering if anybody has done something like
> > this in a pl/pgsql. I guess a procedure like this could be used as a
> > default for a column, and making the column unique could prevent duplicates,
> > although this should ideally be done as part of the generating procedure,
> > not afterwards.
> One option is to use a cipher (such as AES) in counter mode (you need to
> keep the key secret, but that shouldn't be a significant additional risk
> as you are keeping the ids in the database) and use the encrypted string
> to generate the string. The counter can be a sequence. You won't get
> duplicates as longer as the counter isn't reset and you don't change the
I forgot something important. For there not to be duplicates the function
from the encrypted string to the member id needs to be 1-1 (injective).
Your member code has just over 24 bits of information, so you want to find
a cipher that works on blocks of 24 bits. You might have trouble finding
strong ciphers that work on 24 bit blocks.
In response to
pgsql-novice by date
|Next:||From: Roland Giesler||Date: 2005-12-15 04:05:17|
|Subject: Re: Sequence on a char(6) column|
|Previous:||From: Roland Giesler||Date: 2005-12-15 01:47:39|
|Subject: Re: RoR Tutorial|