Thread: Random Unique Id

Hi all,<br />  I was searching for a sequence (for serials) that let me use a random unique number ID on a Primary Key
ora simple index.<br />  I have not found a solution so I have done it by myself. I would like to share it so here it
is:<br/>   <br />-- ----------------------------<br />-- Create language "plpgsql"<br />--
----------------------------<br/>CREATE LANGUAGE plpgsql;<br /><br />-- ----------------------------<br />-- Table
structurefor "public"."tarjeta"<br /> -- ----------------------------<br />drop table "public"."tarjeta";<br />CREATE
TABLE"public"."tarjeta"(<br />"idtarjeta" int8 NOT NULL DEFAULT randomuniqueidtarjeta(),<br />"fechaemision" timestamp
NOTNULL DEFAULT now(),<br /> "descripcion" varchar(255) ,<br />PRIMARY KEY ("idtarjeta")<br />)  WITHOUT OIDS;<br /><br
/>------------------------------<br />-- Definition of function  "randomuniqueidtarjeta"<br />--
----------------------------<br/><br />CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURNS bigint AS $$<br /><br
/>DECLARE<br/><br />    -- SET THE KEY SIZE (IN CHARACTERS)<br />    idSize constant  integer := 10;<br /><br />   
sizeMultiplicatorconstant bigint := 10 ^ idSize;<br /><br />    loopLimit  bigint := sizeMultiplicator * 4;<br />   
randomNumberbigint;<br />    canIUseIt boolean := false;<br /><br />BEGIN<br /><br />    -- LOOP UNTIL FIND AN UNIQUE
RANDOMNUMBER OR FILL THE LOOP LIMIT<br />    WHILE ( not ( canIUseIt ) and ( loopLimit > 0) ) LOOP<br /><br />   
   -- CALCULATE A TEN DIGITS RANDOM NUMBER<br />        randomNumber := CAST ( ( random() * sizeMultiplicator ) AS
bigint);<br /><br />        -- VALIDATE THAT THE NUMBER WON'T START WITH 0<br />        IF ( (randomNumber >=
sizeMultiplicator/ 10 ) and ( randomNumber < sizeMultiplicator ) ) THEN<br /><br />            -- CHECK THAN RANDOM
NUMBERNOT EXISTS ON TABLE<br />            -- IMPORTANT!!! CHANGE WITH TABLE AND FIELD TO SEARCH FOR A DUPLICATATION<br
/>           PERFORM idtarjeta FROM tarjeta WHERE idtarjeta = randomNumber;<br />             IF NOT FOUND THEN<br
/>               canIUseIt = true;<br />            END IF;<br /><br />        END IF;<br /><br />        loopLimit =
loopLimit- 1;<br /><br />    END LOOP;<br /><br />    -- IF LOOP ENDS WITH A UNIQUE RANDOM NUMBER RETURNS THE NUMBER
CONCATENATEDWITH A VERIFICATION NUMBER  ELSE RAISE AN EXCEPTION<br />     IF ( canIUseIt ) THEN<br />        RETURN
CAST( ( CAST ( randomNumber AS text ) || CAST ( verificationNumber( CAST (randomNumber AS text ) ) AS text  ) ) AS 
bigint)  ;<br />    ELSE<br />        RAISE EXCEPTION 'Could not calculate a Random Unique ID on table Tarjeta.';<br />
   END IF;<br /><br />END;<br />$$ LANGUAGE plpgsql;<br /><br />-- ----------------------------<br />-- Definition of
function "randomuniqueidtarjeta"<br />-- ----------------------------<br /><br />CREATE OR REPLACE FUNCTION
verificationNumber(idtext) RETURNS integer AS $$<br /> DECLARE<br /><br />    auxValue integer := 0;<br />   
verifyArrayconstant integer[] :=ARRAY [5,4,3,2,7,6,5,4,3,2,1];<br />    verificationNumber integer := 0;<br /><br
/>BEGIN<br/><br />    -- ASK FOR ID LENGTH EQUAL TO 10 DIGITS. THE ALGORITHM WORKS ONLY FOR <br />     IF ( LENGTH( id
)<> 10 ) THEN<br /><br />        RAISE EXCEPTION 'Could not calculate a verification number. The ID must have 10
digits.';<br/><br />    ELSE<br /><br />        -- LOOP ON THE TEN DIGITS AND MULTIPLY WITH THE SAME POSITION ON
virifyArray<br/>         FOR digit IN 1..10 LOOP<br />            auxValue = auxValue + ( CAST(SUBSTR(id, digit, 1) AS
int)* verifyArray[digit] );<br />        END LOOP;<br /><br />        -- CALCULATE THE VERIFICATION NUMBER<br />       
verificationNumber= 11 - (auxValue % 11);<br /><br />        -- REPLACE THE TWO DIGITS  VERIFICATION NUMBER<br />   
   IF( verificationNumber = 11 ) THEN<br />            RETURN 0;<br />        ELSEIF ( verificationNumber = 10 )
THEN<br/>            RETURN 9;<br />        ELSE<br />             RETURN verificationNumber;<br />        END IF;<br
/><br/>    END IF;<br /><br />END;<br />$$ LANGUAGE plpgsql;<br /><br />-- ----------------------------<br />-- INSERTs
toprobe the functions<br />-- ----------------------------<br /> INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta
1');<br/>INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 2');<br />INSERT INTO tarjeta (descripcion) VALUES
('Tarjeta3');<br />INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 4');<br /> INSERT INTO tarjeta (descripcion)
VALUES('Tarjeta 5');<br />INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 6');<br />INSERT INTO tarjeta
(descripcion)VALUES ('Tarjeta 7');<br />INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 8');<br /> INSERT INTO
tarjeta(descripcion) VALUES ('Tarjeta 9');<br />INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 10');<br /><br /><br
/>     It is my first post so sorry about the format of the SQL Dump and sorry about my english.<br />       Note:
thereis a second function that calculate a verification number like an argentinian code called CUIL (only available for
10digits numbers)<br />      Regards....<br /><br />            Nahuel Alejandro Ramos.<br /><br /> 

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?

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@gmail.com
> <mailto:robjsargent@gmail.com>> 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?
> 
> 

On Tue, 20 Oct 2009 16:49:17 -0300
Nahuel Alejandro Ramos <nahuelon@gmail.com> 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

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