Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Roland GieslerDate: 2005-12-15 04:05:17
Subject: Re: Sequence on a char(6) column
Previous:From: Roland GieslerDate: 2005-12-15 01:47:39
Subject: Re: RoR Tutorial

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group