How to create column in DB with default value random string

Question

Can I create column in DB table (PostgreSQL) which have default value random string, and how ?

If is not possible, please let me know that.

• Jack Douglas: please ask again for MySQL if you need an answer for that RDBMS too
• ma11hew28: UUID solution: stackoverflow.com/a/21684011/242933

Answer 1

PostgreSQL example:

CREATE OR REPLACE FUNCTION f_random_text(
 length integer
)
RETURNS text AS
$body$
WITH chars AS (
 SELECT unnest(string_to_array('A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9', ' ')) AS _char
),
charlist AS
(
 SELECT _char FROM chars ORDER BY random() LIMIT 1ドル
)
SELECT string_agg(_char, '')
FROM charlist
;
$body$
LANGUAGE sql;
DROP TABLE IF EXISTS tmp_test;
CREATE TEMPORARY TABLE tmp_test (
 id serial,
 data text default f_random_text(12)
);
INSERT INTO tmp_test
VALUES 
 (DEFAULT, DEFAULT),
 (DEFAULT, DEFAULT)
;
SELECT * FROM tmp_test;
 id | data
----+--------------
 1 | RYMUJH4E0NIQ
 2 | 7U4029BOKAEJ
(2 rows)

Apparently you can do this. (Of course, you can add other characters as well, or use other random string generator as well - like this, for example.)

Answer 2

The solution is (for PGSQL):

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

• cjauvin: This is a beautiful trick!
• Doron Segal: if you need to limit you can use this: alter TABLE users ADD column register_key text NOT NULL default substring(md5(random()::text), 0, 8);