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

In response to

Responses

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-12-15 00:26:22
Subject: RoR Tutorial
Previous:From: Michael DenglerDate: 2005-12-14 21:30:34
Subject: Static Variables in PL/PGSQL

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