lundi 19 juin 2017

PostgreSQL: Column DEFAULT gen_random_bytes UNIQUE

Let's say we have the following table:

CREATE TABLE foo (
    column_1 bigint,
    column_2 bytea DEFAULT gen_random_bytes(2),
    PRIMARY KEY (column_1, column_2)
);

When inserting a new row into our table foo, we want column_2 to default to a cryptographically strong random number, and we want this number to be unique against column_1.

I guess we'd have to do a loop until gen_random_bytes(2) returns a unique result? If so, can we do this loop with pure SQL, maybe with recursive CTE, instead of with plpgsql?




Aucun commentaire:

Enregistrer un commentaire