SQL Populate table with random data

The following worked for me:

create table t_random as select s, md5(random()::text) from generate_Series(1,5) s;

Here it is a more elegant way using the latest features. I will use the Unix dictionary (/usr/share/dict/words) and copy it into my PostgreSQL data:

cp /usr/share/dict/words data/pg95/words.list

Then, you can easily create a ton of no sense description BUT searchable using dictionary words with the following steps:

1) Create table and function. getNArrayS gets all the elements in an array and teh number of times it needs to concatenate.

CREATE TABLE randomTable(id serial PRIMARY KEY, description text);

CREATE OR REPLACE FUNCTION getNArrayS(el text[], count int) RETURNS text AS $$
  SELECT string_agg(el[random()*(array_length(el,1)-1)+1], ' ') FROM generate_series(1,count) g(i)
$$
VOLATILE
LANGUAGE SQL;

Once you have all in place, run the insert using CTE:

WITH t(ray) AS(
  SELECT (string_to_array(pg_read_file('words.list')::text,E'\n')) 
) 
INSERT INTO randomTable(description)
SELECT getNArrayS(T.ray, 3) FROM T, generate_series(1,10000);

And now, select as usual:

postgres=# select * from randomtable limit 3;
 id |                 description                 
----+---------------------------------------------
  1 | ultracentenarian splenodiagnosis manurially
  2 | insequent monopolarity funipendulous
  3 | ruminate geodic unconcludable
(3 rows)

I assume sentance == statement? You could use perl or plperl as perl has some good random data generators. Check out perl CPAN module Data::Random to start.

Here's a sample of a perl script to generate some different random stuff taken from CPAN.

use Data::Random qw(:all);

  my @random_words = rand_words( size => 10 );

  my @random_chars = rand_chars( set => 'all', min => 5, max => 8 );

  my @random_set = rand_set( set => \@set, size => 5 );

  my $random_enum = rand_enum( set => \@set );

  my $random_date = rand_date();

  my $random_time = rand_time();

  my $random_datetime = rand_datetime();

  open(FILE, ">rand_image.png") or die $!;
  binmode(FILE);
  print FILE rand_image( bgcolor => [0, 0, 0] );
  close(FILE);

Generating random strings with the specified characters and length

The fact that md5(random()::text) only has hex digits and a fixed length saddens me a bit, which made me look for a more elegant function.

This question has some good answers: Generate random String in PostgreSQL I think I'll end up going with the following function:

CREATE OR REPLACE FUNCTION random_string(int) RETURNS TEXT as $$
select
string_agg(substr(characters, (random() * length(characters) + 1)::integer, 1), '') as random_word
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-    ')) as symbols(characters)
join generate_series(1, $1) on 1 = 1
$$ language sql;  

which can then be used as:

CREATE TABLE "mytable" ("i" INTEGER, "j" INTEGER, "s" TEXT, "t" TEXT);
INSERT INTO "mytable" SELECT i, i*2, random_string(10), random_string(20)
  FROM generate_series(1, 10) as s(i);

to produce data like:

 i  | j  |     s      |          t           
----+----+------------+----------------------
  1 |  2 | VgXg6 9c9T | 3SbRe0CmyDzL6tbIi s8
  2 |  4 | 6Nqx ynI7  |  nQhOZrnXI9HdwBlNzFB
  3 |  6 | FXGl4Wn4va | RZ2zdOLFqErJ44MhoQVm
  4 |  8 | 6i  IrvUVE | bY7Gta1 c8tVdDpMTiJ2
  5 | 10 | aU-30dwGVl | mzZPchylUL6FgQ04N Fy
  6 | 12 | yQMnXFPIt  |  1 fMb7wQE-gqCzkFoVD
  7 | 14 | Okj7XypLbQ | TosYftFbbRSD4GnQOELV
  8 | 16 | 1w P3zUW   |  kY1c kU0mJNURCCw1na
  9 | 18 | vYcFmC8W82 | R6Omwp KcL H1MNJKzR
 10 | 20 | v0I fPg32  | 8 cjc0q8DCOczxNNvF4N

Performance-wise it is not amazing however, with about 12s for 1 million rows. For comparison:

INSERT INTO "tmp" (i, j, s, t) SELECT i, i*2, md5(random()::text), md5(random()::text)

took 1.8 s, and:

INSERT INTO "tmp" (i, j, s, t) SELECT i, i*2, 'a', 'a'

took 0.5 s.

Generate a CSV with an external tool and import it with \copy FROM STDIN

Since every pure PostgreSQL option is a bit awkward and/or slow, perhaps the best option is to just use an external tool to generate the data, and then feed it to PostgreSQL as a CSV from stdin.

For example, given the table:

CREATE TABLE "mytable" ("i" INTEGER, "j" INTEGER, "s" TEXT, "t" TEXT);

a potentially slow but convenient option would be to just use a Python script such as:

generate_data.py

import csv
import random
import string
import sys

CHARS = string.ascii_letters + string.digits + ' '
def rands(n):
    return ''.join(random.choice(CHARS) for _ in range(n))
if len(sys.argv) > 1:
    n = int(sys.argv[1])
else:
    n = 10
writer = csv.writer(sys.stdout)
for i in range(n):
    writer.writerow((i, random.randrange(100), rands(10), rands(20)))

and then use it as:

python generate_data.py 10 |
psql mydb -c '\copy "mytable" FROM STDIN'

Related:

• PostgreSQL copy command using STDIN
• How to import CSV file data into a PostgreSQL table
• Random string generation with upper case letters and digits

Tested on Ubuntu 24.04, PostgreSQL 16.6, Python 3.12.3, Lenovo ThinkPad P14s.