How to Generate Random Data in PostgreSQL

By Muhammad Panji |
Tags:
Categories:

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