Most convenient way to generate unique random hash for a column in SQL?-CodePudding

I have a table foo with a non-nullable and unique column called hash. I would like to create a trigger, or default to this column if possible, that generates a unique random 64-character string.

Although it is possible to create a default like such, it does not prevent the low chance of generating a non-unique hash.

alter TABLE foo ADD column register_key text UNIQUE NOT NULL default md5(random()::text);

I am working on creating a trigger in plpgsql but am struggling to compare the hash I generated.

Here is what I attempted to do, I created a function that returns a trigger that would generate the hash

create or replace function generate_hash() returns trigger
as $$
declare temp_hash text;
        counter int;
begin
    while exists(select * from foo where hash = temp_hash) and counter < 10 loop
            select substr(sha512(random()::text), 0, 64) into temp_hash;
            counter := counter   1;
    end loop;
    if counter >= 10 
       then RAISE EXCEPTION 'cannot generate hash';
       end if;
    new.hash = temp_hash;
    return new;
end;
$$ language plpgsql;

and created a trigger on table foo

CREATE TRIGGER insert_hash
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE generate_hash();

When I tested this by inserting a new record, I receive the error ERROR: null value in column "hash" violates not-null constraint.

What did I do wrong? Is there a more appropriate approach than what I am doing?

CodePudding user response：

Since data is inserted one row at a time, you could use a timestamp as value for your hash, as:

alter TABLE foo ADD column register_key varchar(64) 
UNIQUE NOT NULL default encode(digest(now()::text, 'sha256'), 'hex');