Howtodojo

How to Generate Random Data in PostgreSQL

July 1, 2021 in Tutorial1 minute

Overview

In this tutorial we learn how to generate random data in PostgreSQL. This method is sometimes needed to test the functionality of a PostgreSQL installation whether locally or remotely.

Generate Random Data using md5 function in PostgreSQL

To generate

SELECT generate_series(1,10) AS id, md5(random()::text) AS descr;

Generate data using generate_series function

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

Generate data from random words in words.list file

Create table

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

Create function

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;

Insert data

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

source: stackoverflow

PostgreSQL