Example Data

When we need to prepare some fake data in the database to play with.

Random Strings

md5 string

md5 function accepts a text:

md5(random()::TEXT)

Random Numbers

• random() — random numbers

Random Integer ID

round(1000*random())
ceil(1000*random())

Random Elements in array

SELECT (array['a', 'b', 'c'])[ceil(random() * 3)] AS random_element ;

Series of Numbers

• generate_series()

  INSERT INTO users(id) select generate_series(1,1000000) ;
  

• More in Set Returning Functions and Date/Time functions

List all days in May:

SELECT generate_series('2020-05-01'::timestamp, '2020-05-05', '1 day');
   generate_series
---------------------
 2020-05-01 00:00:00
 2020-05-02 00:00:00
 2020-05-03 00:00:00
 2020-05-04 00:00:00
 2020-05-05 00:00:00
(5 rows)

SELECT generate_series('2020-05-01'::date, '2020-05-05', '1 day');
    generate_series
------------------------
 2020-05-01 00:00:00+09
 2020-05-02 00:00:00+09
 2020-05-03 00:00:00+09
 2020-05-04 00:00:00+09
 2020-05-05 00:00:00+09
(5 rows)

An example

CREATE TABLE posts (
  id bigint,
  created_at timestamp(6) without time zone
);

INSERT INTO posts(id, created_at)
SELECT round(1000*random()), generate_series('2020-05-01'::timestamp, '2020-05-31', '1 minute');

SELECT * FROM posts limit 5;
 id  |     created_at
-----+---------------------
 703 | 2020-05-01 00:00:00
 779 | 2020-05-01 00:01:00
 580 | 2020-05-01 00:02:00
 765 | 2020-05-01 00:03:00
 303 | 2020-05-01 00:04:00
(5 rows)

For loop

DO $$
BEGIN
FOR n IN 1..1000 LOOP
-- Add your statement here
END LOOP;
END;
$$;