Thanks for that link Depesz!
It worked, I've run ALTER TABLE with your function and didn't have collisions.
I guess it's more bulletproof because random() is called not once, but
for every character therefore reducing possibility of collision by
multitude of number of bytes in hash.
CREATE OR REPLACE FUNCTION make_random_string(string_length INT4)
RETURNS TEXT
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
possible_chars TEXT =
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
output TEXT = '';
i INT4;
pos INT4;
BEGIN
FOR i IN 1..string_length LOOP
pos := 1 + cast( random() * ( length(possible_chars) - 1) as INT4 );
output := output || substr(possible_chars, pos, 1);
END LOOP;
RETURN output;
END;
$BODY$;
CREATE TABLE item
(
item_id bigserial NOT NULL,
title character varying,
CONSTRAINT pk PRIMARY KEY (item_id)
)
WITH (
OIDS=FALSE
);
....
LOOP
INSERT INTO item(
title)
VALUES ('title1');
count = count+1;
EXIT WHEN count > 10000000;
END LOOP;
....
ALTER TABLE item ADD COLUMN hash1 character varying NOT NULL DEFAULT
make_random_string(64);
ALTER TABLE item ADD UNIQUE (hash1);
Query returned successfully with no result in 2120670 ms.
It worked! No collisions on 10 million records.
Now a question. Is it okay to add calculated column this way by
specifying DEFAULT. Or I'm better using INSERT trigger? is DEFAULT
basically an internal insert trigger?
Thanks.
On Fri, Jan 29, 2010 at 10:50 PM, hubert depesz lubaczewski
<depesz@xxxxxxxxxx> wrote:
> On Fri, Jan 29, 2010 at 07:20:33PM +1100, Joe Kramer wrote:
>> I need to generate unique id which is not guessable unlike
>> serial(integer) type. I need an id in format like md5 hash of random
>> number.
>
> check this blogpost:
> http://www.depesz.com/index.php/2007/06/25/random-text-record-identifiers/
>
>> On top of that I need this id to be unique across multiple tables.
>
> just add table id to the generated id.
>
> for example: id "xxx" in table users, is globally unique (for your
> database) when you write it: "users:xxx"
>
> if, for some weird reason, you don't want to put table name on its own
> in your key (why?) then just use some dictionary table, that will link
> those keys with your table.
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: depesz@xxxxxxxxxx / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
