19

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.

2

2 Answers 2

12

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.)

1
  • 2
    SELECT md5(random()::text); - That's it, thanks!
    – tasmaniski
    Commented Jun 21, 2012 at 13:04
27

The solution is (for PGSQL):

alter TABLE users ADD column register_key text NOT NULL default md5(random()::text);
2
  • 1
    This is a beautiful trick!
    – cjauvin
    Commented May 27, 2018 at 15:20
  • 3
    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); Commented Dec 28, 2020 at 5:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.