From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Roland Giesler <roland(at)giesler(dot)za(dot)net>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Sequence on a char(6) column |
Date: | 2005-12-15 03:10:48 |
Message-ID: | 20051215031048.GA23362@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Roland Giesler | 2005-12-15 04:05:17 | Re: Sequence on a char(6) column |
Previous Message | Roland Giesler | 2005-12-15 01:47:39 | Re: RoR Tutorial |