Re: Generating unique values for TEXT columns

On Mon, 2005-01-03 at 10:53, Frank D. Engel, Jr. wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Is there any "convenient" way to generate (on request) a unique value
> for a TEXT column? I have a situation in which I want users of my
> front-end program to be able to manually enter values for this column,
> but if they leave it blank (in the front-end), to have the database
> automatically fill in a unique value. I would like to restrict the
> unique values to (for example) digits and uppercase letters (this is
> flexible, but the uniqueness of the values should be visually
> discernible, and all characters should be printable).
>
> I know how to do this with a numeric column (I can just SELECT MAX on
> the column and add one, for example), but how can this be done with a
> TEXT column?

You might want to try creating a sequence for this, then doing something
like:

select upper(md5(nextval('lll')));

Then checking to see if that string is already used to be sure and use
that. If md5 strings are too long, then just substr() the function
above to get a small bit. Chances of failing uniqueness test will
increase, but you have to test for that anyway, so...