Re: Sequence on a char(6) column

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.

In response to

Responses

Browse pgsql-novice by date

  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