Random Unique Id
|
View:
New views
7 Messages
—
Rating Filter:
Alert me
|
 |
Random Unique Id
by Nahuel Alejandro Ramos
::
Rate this Message:
Hi all,
I was searching for a sequence (for serials) that let me use a random unique number ID on a Primary Key or a simple index. I have not found a solution so I have done it by myself. I would like to share it so here it is: -- ---------------------------- -- Create language "plpgsql" -- ---------------------------- CREATE LANGUAGE plpgsql; -- ---------------------------- -- Table structure for "public"."tarjeta" -- ---------------------------- drop table "public"."tarjeta"; CREATE TABLE "public"."tarjeta"( "idtarjeta" int8 NOT NULL DEFAULT randomuniqueidtarjeta(), "fechaemision" timestamp NOT NULL DEFAULT now(), "descripcion" varchar(255) , PRIMARY KEY ("idtarjeta") ) WITHOUT OIDS; -- ---------------------------- -- Definition of function "randomuniqueidtarjeta" -- ---------------------------- CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURNS bigint AS $$ DECLARE -- SET THE KEY SIZE (IN CHARACTERS) idSize constant integer := 10; sizeMultiplicator constant bigint := 10 ^ idSize; loopLimit bigint := sizeMultiplicator * 4; randomNumber bigint; canIUseIt boolean := false; BEGIN -- LOOP UNTIL FIND AN UNIQUE RANDOM NUMBER OR FILL THE LOOP LIMIT WHILE ( not ( canIUseIt ) and ( loopLimit > 0) ) LOOP -- CALCULATE A TEN DIGITS RANDOM NUMBER randomNumber := CAST ( ( random() * sizeMultiplicator ) AS bigint ); -- VALIDATE THAT THE NUMBER WON'T START WITH 0 IF ( (randomNumber >= sizeMultiplicator / 10 ) and ( randomNumber < sizeMultiplicator ) ) THEN -- CHECK THAN RANDOM NUMBER NOT EXISTS ON TABLE -- IMPORTANT!!! CHANGE WITH TABLE AND FIELD TO SEARCH FOR A DUPLICATATION PERFORM idtarjeta FROM tarjeta WHERE idtarjeta = randomNumber; IF NOT FOUND THEN canIUseIt = true; END IF; END IF; loopLimit = loopLimit - 1; END LOOP; -- IF LOOP ENDS WITH A UNIQUE RANDOM NUMBER RETURNS THE NUMBER CONCATENATED WITH A VERIFICATION NUMBER ELSE RAISE AN EXCEPTION IF ( canIUseIt ) THEN RETURN CAST ( ( CAST ( randomNumber AS text ) || CAST ( verificationNumber( CAST (randomNumber AS text ) ) AS text ) ) AS bigint ) ; ELSE RAISE EXCEPTION 'Could not calculate a Random Unique ID on table Tarjeta.'; END IF; END; $$ LANGUAGE plpgsql; -- ---------------------------- -- Definition of function "randomuniqueidtarjeta" -- ---------------------------- CREATE OR REPLACE FUNCTION verificationNumber(id text) RETURNS integer AS $$ DECLARE auxValue integer := 0; verifyArray constant integer[] :=ARRAY [5,4,3,2,7,6,5,4,3,2,1]; verificationNumber integer := 0; BEGIN -- ASK FOR ID LENGTH EQUAL TO 10 DIGITS. THE ALGORITHM WORKS ONLY FOR IF ( LENGTH( id ) <> 10 ) THEN RAISE EXCEPTION 'Could not calculate a verification number. The ID must have 10 digits.'; ELSE -- LOOP ON THE TEN DIGITS AND MULTIPLY WITH THE SAME POSITION ON virifyArray FOR digit IN 1..10 LOOP auxValue = auxValue + ( CAST(SUBSTR(id, digit, 1) AS int) * verifyArray[digit] ); END LOOP; -- CALCULATE THE VERIFICATION NUMBER verificationNumber = 11 - (auxValue % 11); -- REPLACE THE TWO DIGITS VERIFICATION NUMBER IF( verificationNumber = 11 ) THEN RETURN 0; ELSEIF ( verificationNumber = 10 ) THEN RETURN 9; ELSE RETURN verificationNumber; END IF; END IF; END; $$ LANGUAGE plpgsql; -- ---------------------------- -- INSERTs to probe the functions -- ---------------------------- INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 1'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 2'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 3'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 4'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 5'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 6'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 7'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 8'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 9'); INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 10'); It is my first post so sorry about the format of the SQL Dump and sorry about my english. Note: there is a second function that calculate a verification number like an argentinian code called CUIL (only available for 10 digits numbers) Regards.... Nahuel Alejandro Ramos. |
|
|
Re: Random Unique Id
by hazlup
::
Rate this Message:
Nahuel Alejandro Ramos wrote: > Hi all, > I was searching for a sequence (for serials) that let me use a random > unique number ID on a Primary Key or a simple index. > I have not found a solution so I have done it by myself. I would like > to share it so here it is: > > -- ---------------------------- > -- Create language "plpgsql" > -- ---------------------------- > CREATE LANGUAGE plpgsql; > > -- ---------------------------- > -- Table structure for "public"."tarjeta" > -- ---------------------------- > drop table "public"."tarjeta"; > CREATE TABLE "public"."tarjeta"( > "idtarjeta" int8 NOT NULL DEFAULT randomuniqueidtarjeta(), > "fechaemision" timestamp NOT NULL DEFAULT now(), > "descripcion" varchar(255) , > PRIMARY KEY ("idtarjeta") > ) WITHOUT OIDS; > > -- ---------------------------- > -- Definition of function "randomuniqueidtarjeta" > -- ---------------------------- > > CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURNS bigint AS $$ > > DECLARE > > -- SET THE KEY SIZE (IN CHARACTERS) > idSize constant integer := 10; > > sizeMultiplicator constant bigint := 10 ^ idSize; > > loopLimit bigint := sizeMultiplicator * 4; > randomNumber bigint; > canIUseIt boolean := false; > > BEGIN > > -- LOOP UNTIL FIND AN UNIQUE RANDOM NUMBER OR FILL THE LOOP LIMIT > WHILE ( not ( canIUseIt ) and ( loopLimit > 0) ) LOOP > > -- CALCULATE A TEN DIGITS RANDOM NUMBER > randomNumber := CAST ( ( random() * sizeMultiplicator ) AS bigint ); > > -- VALIDATE THAT THE NUMBER WON'T START WITH 0 > IF ( (randomNumber >= sizeMultiplicator / 10 ) and ( > randomNumber < sizeMultiplicator ) ) THEN > > -- CHECK THAN RANDOM NUMBER NOT EXISTS ON TABLE > -- IMPORTANT!!! CHANGE WITH TABLE AND FIELD TO SEARCH FOR A > DUPLICATATION > PERFORM idtarjeta FROM tarjeta WHERE idtarjeta = randomNumber; > IF NOT FOUND THEN > canIUseIt = true; > END IF; > > END IF; > > loopLimit = loopLimit - 1; > > END LOOP; > > -- IF LOOP ENDS WITH A UNIQUE RANDOM NUMBER RETURNS THE NUMBER > CONCATENATED WITH A VERIFICATION NUMBER ELSE RAISE AN EXCEPTION > IF ( canIUseIt ) THEN > RETURN CAST ( ( CAST ( randomNumber AS text ) || CAST ( > verificationNumber( CAST (randomNumber AS text ) ) AS text ) ) AS > bigint ) ; > ELSE > RAISE EXCEPTION 'Could not calculate a Random Unique ID on table > Tarjeta.'; > END IF; > > END; > $$ LANGUAGE plpgsql; > > -- ---------------------------- > -- Definition of function "randomuniqueidtarjeta" > -- ---------------------------- > > CREATE OR REPLACE FUNCTION verificationNumber(id text) RETURNS integer AS $$ > DECLARE > > auxValue integer := 0; > verifyArray constant integer[] :=ARRAY [5,4,3,2,7,6,5,4,3,2,1]; > verificationNumber integer := 0; > > BEGIN > > -- ASK FOR ID LENGTH EQUAL TO 10 DIGITS. THE ALGORITHM WORKS ONLY FOR > IF ( LENGTH( id ) <> 10 ) THEN > > RAISE EXCEPTION 'Could not calculate a verification number. The > ID must have 10 digits.'; > > ELSE > > -- LOOP ON THE TEN DIGITS AND MULTIPLY WITH THE SAME POSITION ON > virifyArray > FOR digit IN 1..10 LOOP > auxValue = auxValue + ( CAST(SUBSTR(id, digit, 1) AS int) * > verifyArray[digit] ); > END LOOP; > > -- CALCULATE THE VERIFICATION NUMBER > verificationNumber = 11 - (auxValue % 11); > > -- REPLACE THE TWO DIGITS VERIFICATION NUMBER > IF( verificationNumber = 11 ) THEN > RETURN 0; > ELSEIF ( verificationNumber = 10 ) THEN > RETURN 9; > ELSE > RETURN verificationNumber; > END IF; > > END IF; > > END; > $$ LANGUAGE plpgsql; > > -- ---------------------------- > -- INSERTs to probe the functions > -- ---------------------------- > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 1'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 2'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 3'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 4'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 5'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 6'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 7'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 8'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 9'); > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 10'); > > > It is my first post so sorry about the format of the SQL Dump and > sorry about my english. > Note: there is a second function that calculate a verification > number like an argentinian code called CUIL (only available for 10 > digits numbers) > Regards.... > > Nahuel Alejandro Ramos. > You didn't like UUID? -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
|
|
Re: Random Unique Id
by Nahuel Alejandro Ramos
::
Rate this Message:
Yes. I looked this solution but it is not a "only numbers" ID. I would like a random unique "number" Id. For example: generate a credit number randomly (like the example I post).
I used to insert an MD5 field but this time I need "only numbers" Id. Regards... Nahuel Alejandro Ramos. On Tue, Oct 20, 2009 at 4:53 PM, Rob Sargent <robjsargent@...> wrote:
|
|
|
Re: Random Unique Id
by hazlup
::
Rate this Message:
Suit yourself, of course, but the numbers on my credit cards are far,
far from random :) Nahuel Alejandro Ramos wrote: > Yes. I looked this solution but it is not a "only numbers" ID. I would > like a random unique "number" Id. For example: generate a credit number > randomly (like the example I post). > I used to insert an MD5 field but this time I need "only numbers" Id. > Regards... > > Nahuel Alejandro Ramos. > > > On Tue, Oct 20, 2009 at 4:53 PM, Rob Sargent <robjsargent@... > <mailto:robjsargent@...>> wrote: > > > > Nahuel Alejandro Ramos wrote: > > Hi all, > > I was searching for a sequence (for serials) that let me use a > random > > unique number ID on a Primary Key or a simple index. > > I have not found a solution so I have done it by myself. I would > like > > to share it so here it is: > > > > -- ---------------------------- > > -- Create language "plpgsql" > > -- ---------------------------- > > CREATE LANGUAGE plpgsql; > > > > -- ---------------------------- > > -- Table structure for "public"."tarjeta" > > -- ---------------------------- > > drop table "public"."tarjeta"; > > CREATE TABLE "public"."tarjeta"( > > "idtarjeta" int8 NOT NULL DEFAULT randomuniqueidtarjeta(), > > "fechaemision" timestamp NOT NULL DEFAULT now(), > > "descripcion" varchar(255) , > > PRIMARY KEY ("idtarjeta") > > ) WITHOUT OIDS; > > > > -- ---------------------------- > > -- Definition of function "randomuniqueidtarjeta" > > -- ---------------------------- > > > > CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURNS bigint > AS $$ > > > > DECLARE > > > > -- SET THE KEY SIZE (IN CHARACTERS) > > idSize constant integer := 10; > > > > sizeMultiplicator constant bigint := 10 ^ idSize; > > > > loopLimit bigint := sizeMultiplicator * 4; > > randomNumber bigint; > > canIUseIt boolean := false; > > > > BEGIN > > > > -- LOOP UNTIL FIND AN UNIQUE RANDOM NUMBER OR FILL THE LOOP LIMIT > > WHILE ( not ( canIUseIt ) and ( loopLimit > 0) ) LOOP > > > > -- CALCULATE A TEN DIGITS RANDOM NUMBER > > randomNumber := CAST ( ( random() * sizeMultiplicator ) AS > bigint ); > > > > -- VALIDATE THAT THE NUMBER WON'T START WITH 0 > > IF ( (randomNumber >= sizeMultiplicator / 10 ) and ( > > randomNumber < sizeMultiplicator ) ) THEN > > > > -- CHECK THAN RANDOM NUMBER NOT EXISTS ON TABLE > > -- IMPORTANT!!! CHANGE WITH TABLE AND FIELD TO SEARCH > FOR A > > DUPLICATATION > > PERFORM idtarjeta FROM tarjeta WHERE idtarjeta = > randomNumber; > > IF NOT FOUND THEN > > canIUseIt = true; > > END IF; > > > > END IF; > > > > loopLimit = loopLimit - 1; > > > > END LOOP; > > > > -- IF LOOP ENDS WITH A UNIQUE RANDOM NUMBER RETURNS THE NUMBER > > CONCATENATED WITH A VERIFICATION NUMBER ELSE RAISE AN EXCEPTION > > IF ( canIUseIt ) THEN > > RETURN CAST ( ( CAST ( randomNumber AS text ) || CAST ( > > verificationNumber( CAST (randomNumber AS text ) ) AS text ) ) AS > > bigint ) ; > > ELSE > > RAISE EXCEPTION 'Could not calculate a Random Unique ID on > table > > Tarjeta.'; > > END IF; > > > > END; > > $$ LANGUAGE plpgsql; > > > > -- ---------------------------- > > -- Definition of function "randomuniqueidtarjeta" > > -- ---------------------------- > > > > CREATE OR REPLACE FUNCTION verificationNumber(id text) RETURNS > integer AS $$ > > DECLARE > > > > auxValue integer := 0; > > verifyArray constant integer[] :=ARRAY [5,4,3,2,7,6,5,4,3,2,1]; > > verificationNumber integer := 0; > > > > BEGIN > > > > -- ASK FOR ID LENGTH EQUAL TO 10 DIGITS. THE ALGORITHM WORKS > ONLY FOR > > IF ( LENGTH( id ) <> 10 ) THEN > > > > RAISE EXCEPTION 'Could not calculate a verification > number. The > > ID must have 10 digits.'; > > > > ELSE > > > > -- LOOP ON THE TEN DIGITS AND MULTIPLY WITH THE SAME > POSITION ON > > virifyArray > > FOR digit IN 1..10 LOOP > > auxValue = auxValue + ( CAST(SUBSTR(id, digit, 1) AS > int) * > > verifyArray[digit] ); > > END LOOP; > > > > -- CALCULATE THE VERIFICATION NUMBER > > verificationNumber = 11 - (auxValue % 11); > > > > -- REPLACE THE TWO DIGITS VERIFICATION NUMBER > > IF( verificationNumber = 11 ) THEN > > RETURN 0; > > ELSEIF ( verificationNumber = 10 ) THEN > > RETURN 9; > > ELSE > > RETURN verificationNumber; > > END IF; > > > > END IF; > > > > END; > > $$ LANGUAGE plpgsql; > > > > -- ---------------------------- > > -- INSERTs to probe the functions > > -- ---------------------------- > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 1'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 2'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 3'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 4'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 5'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 6'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 7'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 8'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 9'); > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 10'); > > > > > > It is my first post so sorry about the format of the SQL > Dump and > > sorry about my english. > > Note: there is a second function that calculate a verification > > number like an argentinian code called CUIL (only available for 10 > > digits numbers) > > Regards.... > > > > Nahuel Alejandro Ramos. > > > > You didn't like UUID? > > -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
|
|
Re: Random Unique Id
by Nahuel Alejandro Ramos
::
Rate this Message:
:D, Yeah, i know. Its only an example. We are using this algorithm to give a random unique Id to our clients. The need was to give a PK absolute independent of time. Thanks to Ivan, for the pseudo-random posted, I am looking it.
Regards... Nahuel Alejandro Ramos. On Tue, Oct 20, 2009 at 5:03 PM, Rob Sargent <robjsargent@...> wrote: Suit yourself, of course, but the numbers on my credit cards are far, |
|
|
Re: Random Unique Id
by Ivan Sergio Borgonovo
::
Rate this Message:
On Tue, 20 Oct 2009 16:49:17 -0300
Nahuel Alejandro Ramos <nahuelon@...> wrote: > Hi all, > I was searching for a sequence (for serials) that let me use a > random unique number ID on a Primary Key or a simple index. > I have not found a solution so I have done it by myself. I would > like to share it so here it is: http://www.webthatworks.it/d1/node/page/pseudo_random_sequences_postgresql Adapted from Daniel Verite suggestion. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
|
|
Re: Random Unique Id
by Craig Ringer
::
Rate this Message:
Nahuel Alejandro Ramos wrote:
> Hi all, > I was searching for a sequence (for serials) that let me use a random > unique number ID on a Primary Key or a simple index. > I have not found a solution so I have done it by myself. I would like to > share it so here it is: Here's what I'm using: http://wiki.postgresql.org/wiki/Pseudo_encrypt thanks to the incredibly helpful folks on this list, in this case particularly Daniel Verite. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql |
| Free embeddable forum powered by Nabble | Forum Help |
