Generate Random Text Strings in PostgreSQL

Need a quick and easy way to generate a random string to fill in a text column, say for generating synthetic data or anonymizing data? Consider using the Orafce extension, which includes a function for PostgreSQL that generates random strings.

Simply install this extension on your PostgreSQL database, or, if you are using a PostgreSQL-compatible database, the extension may already be installed. To use Orafce in those environments, simply execute the CREATE EXTENSION command (see below) and check the arguments.

yugabyte=# create extension if not exists orafce;

CREATE EXTENSION

yugabyte=# \df dbms_random.string;

                           List of functions
   Schema    |  Name  | Result data type |  Argument data types  | Type
-------------+--------+------------------+-----------------------+------
 dbms_random | string | text             | opt text, len integer | func
(1 row)

The second parameter is the length of the random text you want to generate. The first parameter is a case-insensitive character that defines the subset of ASCII characters:

• P: printable characters (ASCII between 0x0020 and 0x007D) like the regexp [ -~]
• X: only alpha num in upper case like the regexp [0-9A-Z]
• A: only alphabetical in upper and lower case like the regexp [a-zA-Z]
• U: only upper case alphabetical like the regexp [A-Z]
• L: only lower case alphabetical like the regexp [a-z]

Here is an example using YugabyteDB which has orafce installed by default:

yugabyte=# select opt,dbms_random.string(opt,42) 
           from regexp_split_to_table('PXAUL','') opt;

 opt |                   string
-----+--------------------------------------------
 P   | yV;r^<$GUTB>c/cp; a}=`"@G`F`)Q2D^z<8l!C3eJ
 X   | 0KBFTOF3FK3A2WAUWVHXN0BM4N40PAUQU55OTKR84U
 A   | BjMQbGJzCsBSYGBeHmtyXmGfVxIIKOAlYmcZSLzvea
 U   | HBQUDHANTZTJCRVTINOWTNCUNYNZJPAQQQLUYMIRMC
 L   | bouxidkwzejbyxamwjcxatnlnlywdkyfyschvndvrn
(5 rows)

Orafce provides some Oracle-compatible functions to ease the migration from Oracle Database to OpenSource PostgreSQL or a PostgreSQL-compatible database like YugabyteDB.

There are two differences with the Oracle database. Please note that I’ve opened two issues, and both were fixed quickly. I’m only mentioning them here to provide a workaround in case the fix has not been released.

The first deals with the printable set of characters where Oracle can return \ and ~ but not Orafce. The Github issue is #223. The difference in Oracle’s behavior should not matter and can be ignored.

The second issue, #222, is about multiple calls to dbms_random in one statement, where the function is executed once, and the result is cached for the next calls:

yugabyte=# select dbms_random.string('L',42) 
           from generate_series(1,5);

                   string
--------------------------------------------
 dtnbtmvwbjdznyylggjwskondcmbxrebkrcdexafhe
 dtnbtmvwbjdznyylggjwskondcmbxrebkrcdexafhe
 dtnbtmvwbjdznyylggjwskondcmbxrebkrcdexafhe
 dtnbtmvwbjdznyylggjwskondcmbxrebkrcdexafhe
 dtnbtmvwbjdznyylggjwskondcmbxrebkrcdexafhe
(5 rows)

This is easy to work around—making the function volatile instead of immutable (the equivalent of deterministic in Oracle):

yugabyte=# alter function dbms_random.string volatile;
ALTER FUNCTION  

yugabyte=# select dbms_random.string('L',42) 
           from generate_series(1,5);

                   string
--------------------------------------------
 fucdgjkpfczuqcxceutiveayhevhjdmoxosexdtdft
 xvvvyapsjlwjjenfmxiymgmfkkieooxmjthitxacix
 lrbzxnwfljmyowiwbxkyjurrdkodnwayobylpvraee
 ysbhpceaaousfxdtbqqcpfenrtijunntgoavqfwruq
 jzomtqcktrqyfhroqmbefisfdjlzafqjffwzwzkpqa
(5 rows)

This fix for the issue is already committed so doublecheck that you even need this workaround in your release:

yugabyte=# \df+ dbms_random.string;
                                              List of functions
   Schema    |  Name  | Result | | Type | Volatility |
    Description
-------------+--------+--------+-+------+------------+-
 dbms_random | string | text   | | func | immutable  |
(1 row)

yugabyte=# alter function dbms_random.string volatile;

ALTER FUNCTION
yugabyte=# \df+ dbms_random.string;
                                              List of functions
   Schema    |  Name  | Result | | Type | Volatility |
    Description
-------------+--------+--------+-+------+------------+-
 dbms_random | string | text   | | func | volatile   |

(1 row)

yugabyte=#

When creating the extension, the Orafce functions are deployed to schemas with the same name as the Oracle package they emulate. There are many functions in Orafce that are not only useful when migrating from Oracle but are also convenient utility functions to enhance the PostgreSQL ones.