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.
-
please ask again for MySQL if you need an answer for that RDBMS tooJack Douglas– Jack Douglas2013-01-12 09:49:53 +00:00Commented Jan 12, 2013 at 9:49
-
UUID solution: stackoverflow.com/a/21684011/242933ma11hew28– ma11hew282014-02-11 00:16:08 +00:00Commented Feb 11, 2014 at 0:16
Add a comment
|
2 Answers
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.)
The solution is (for PGSQL):
alter TABLE users ADD column register_key text NOT NULL default md5(random()::text);
-
1This is a beautiful trick!cjauvin– cjauvin2018-05-27 15:20:23 +00:00Commented May 27, 2018 at 15:20
-
3if 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);Doron Segal– Doron Segal2020-12-28 05:18:02 +00:00Commented Dec 28, 2020 at 5:18