From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Roland Giesler <roland(at)giesler(dot)za(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Sequence on a char(6) column |
Date: | 2005-12-14 22:22:54 |
Message-ID: | 20051214222254.GA6912@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | operationsengineer1 | 2005-12-15 00:26:22 | RoR Tutorial |
Previous Message | Michael Dengler | 2005-12-14 21:30:34 | Static Variables in PL/PGSQL |