Re: Generating unique values for TEXT columns - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That sounds promising, but I was hoping to avoid a custom function.  Oh
well, I'll get to work on it when I get back from lunch.

If this is done as a stored procedure, won't transaction semantics
prevent it from being a problem when two transactions attempt this
simultaneously?  I'd thought that the output of one would become the
input of the other (in essence, in terms of database state)?


On Jan 3, 2005, at 12:25 PM, Pierre-Frédéric Caillaud wrote:

>
> SELECT max, then treat the string as a sequence of characters and
> increment the last character, rippling the carry if there is one :
>
> carry = 1
> l = len(s)-1
> while carry and l>=0:
>     c = s[l]
>     c += carry
>     if c>max_allowed_char:
>         c = min_allowed_char
>         carry = 1
>     else:
>         carry = 0
>     s[l] = c
>
> if carry:
>     s = min_allowed_char + s
>
> If two transactions do the same at the same time, you're out of luck
> though !
>
> *** Better solution :
>
> if the value was human-entered, prefix it with 'H',
> if it's auto generated, use 'A' concatenated with the value from a
> sequence
>
> thus the user-entered values can't clahs with the sequence values ;
> the sequence values are by definition unique ; and all is well.
>
> or something like that...
>
> On Mon, 3 Jan 2005 11:53:44 -0500, Frank D. Engel, Jr.
> <fde101@fjrhome.net> 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?
>>
>> Thank you!
>>
>> - -----------------------------------------------------------
>> Frank D. Engel, Jr.  <fde101@fjrhome.net>
>>
>> $ ln -s /usr/share/kjvbible /usr/manual
>> $ true | cat /usr/manual | grep "John 3:16"
>> John 3:16 For God so loved the world, that he gave his only begotten
>> Son, that whosoever believeth in him should not perish, but have
>> everlasting life.
>> $ -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.2.4 (Darwin)
>>
>> iD8DBQFB2XiY7aqtWrR9cZoRAqATAJ46xJ3bwsZVpIda0iMSSdcm3/8YKACePSC3
>> 2JAngWmFOlkzC5fNE6HKYMU=
>> =pblY
>> -----END PGP SIGNATURE-----
>>
>>
>>
>> ___________________________________________________________
>> $0 Web Hosting with up to 120MB web space, 1000 MB Transfer
>> 10 Personalized POP and Web E-mail Accounts, and much more.
>> Signup at www.doteasy.com
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
>      joining column's datatypes do not match
>
>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB2YZX7aqtWrR9cZoRAktzAJ0edjYBm7wS/fNtPUt7VIytdAcymACfWO2i
arL1gXIctDZKeqjq6RoILOg=
=BUlk
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com