Обсуждение: Generating unique values for TEXT columns
-----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
Hi Frank
I use the following constructs to generate an objectid's in my database:
CREATE SEQUENCE public.tsfraction MAXVALUE 999999;
CREATE FUNCTION getobjectid() RETURNS text
AS '
select((select(to_char(current_timestamp, \'yyyy-mm-dd-hh-mm-ss\'))) ||
(select(to_char((nextval(\'tsfraction\')),\'-FM000000MI\'))))as return;
'
LANGUAGE 'sql';
CREATE TABLE public.object
(
objectid text NOT NULL DEFAULT getobjectid(),
-- other columns omited
CONSTRAINT pk_object PRIMARY KEY (objectid)
) WITH OIDS;
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl
Frank,
El 03/01/2005 10:53 AM, Frank D. Engel, Jr. en su mensaje escribio:
> 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?
I have plpgsql function to generate random character ids:
CREATE OR REPLACE FUNCTION "public"."basex" (integer, varchar) RETURNS
varchar AS'
DECLARE
lnval ALIAS for $1;
tcdom alias for $2;
lndomsiz integer;
lndig integer;
lcret varchar;
lnval2 integer ;
lnpos integer;
lcdig varchar;
BEGIN
lndomsiz := char_length(tcdom) ;
lnVal2 := lnVal;
lcret :='''';
while lnVal2 <> 0 loop
lndig := lnVal2 % lnDomSiz ;
lnval2 := trunc ( lnVal2/lnDomSiz ) ;
lnpos := lnDig+1 ;
lcdig := substr(tcdom,lnpos,1);
lcret := lcdig || lcret ;
end loop;
return lcret;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Usually I call it this way:
select lpad(basex(nextval('sqrefno')::int,'12456789CFHRWY'),6,'0');
--
Sinceramente,
Josué Maldonado.
"Que se me den seis líneas escritas de puño y letra del hombre más
honrado del mundo, y hallaré en ellas motivos para hacerle ahorcar."
--cardenal Richelieu (Cardenal y político francés. 1.585 - 1.642)
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...
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
>
-----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
> 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)?
Well, for this you need to use a sequence somewhere, or be prepared to
retry on error.
Same thing for :
- read key entered from user
- check if it's in the db
- if it's not, then insert
Is not safe because somebody else may have entered the same key between
the check and the insert. So in a sense the check is useless : just insert
and see if it fails or not. However, the 'max' thingy is guaranteed to
step on other's toes if used concurrently. Which is a lot worse.
Generally, when you design for concurrent systems, you have two choices :
- use a system primitive that supports concurrency and does almost what
you want, and add a thin layer of code on it (like, putting auto and user
generated keys in separate keyspaces by using different prefixes)
- try to design a primitive yourself ; which is generally complex because
you'll have to lock, unlock, and manage concurrency yourself. So unless
there is no other way, this is a slippy slope !
That's why the 'max' solution looks nice, but in fact does not work ;
while the 'prefix' solution looks a bit uglier, but it works, and in fact
it's nicer because it also memorizes whereas the key was generated or not.
>
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>