Re: Sequence on a char(6) column

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.