Re: Sequence on a char(6) column

On Wed, Dec 14, 2005 at 16:22:54 -0600,
  Bruno Wolff III <bruno@wolff.to> wrote:
> On Wed, Dec 14, 2005 at 16:23:14 +0200,
>   Roland Giesler <roland@giesler.za.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
> key.

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.