Generate random elements in PostgreSQL

Question

I have some problem.

I have a small table below:

CREATE TABLE person (
    person_id SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    patronymic TEXT,
    birth_date TIMESTAMP,
    photo_id INTEGER UNIQUE
);

I need to check how some my screepts work with lots of data. So I need to generate a lot of random data with PostgreSQL tools. Like

INSERT INTO MY_TABLE(id,name,...)
SELECT id, md5(random()::text)
FROM generate_series(1,100) id;

But how can I do it whith TIMESTAMP and INTEGER UNIC?

Does this answer your question? How can I generate big data sample for Postgresql using generate_series and random? — Marcus Vinicius Pompeu
– Marcus Vinicius Pompeu, Commented Jan 9, 2022 at 18:08

Erwin Brandstetter · Accepted Answer · 2022-01-09 01:13:51Z

2

For example:

INSERT INTO person (id, name, ..., birth_date, photo_id)
SELECT g AS id
     , md5(random()::text) AS name
     , ...
     , LOCALTIMESTAMP - interval '100 years' * random() AS birth_date
     , g + 12345 AS photo_id    -- also UNIQUE
FROM   generate_series(1,100) g;

Why timestamp and not date for "birth_date"?

answered Jan 9, 2022 at 1:13

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LukStorms · Accepted Answer · 2022-01-09 16:35:27Z

To generate random alien names, birth dates, photo id's

CREATE TABLE person (
    person_id SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    patronymic TEXT,
    birth_date DATE,
    photo_id INTEGER UNIQUE
);

CREATE OR REPLACE FUNCTION base26_encode(IN digits bigint, IN min_width int = 0)
  RETURNS varchar AS $$
        DECLARE
          chars char[];
          ret varchar;
          val bigint;
      BEGIN
      chars := ARRAY['A','B','C','D','E','F','G','H','I','J','K','L','M'
                    ,'N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
      val := digits;
      ret := '';
      IF val < 0 THEN
          val := val * -1;
      END IF;
      WHILE val != 0 LOOP
          ret := chars[(val % 26)+1] || ret;
          val := val / 26;
      END LOOP;

      IF min_width > 0 AND char_length(ret) < min_width THEN
          ret := lpad(ret, min_width, '0');
      END IF;

      RETURN ret;
 
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE;

INSERT INTO person (
 first_name, 
 last_name, 
 patronymic, 
 birth_date, 
 photo_id
)
SELECT 
  initcap(base26_encode(substring(random()::text,3,10)::bigint)) as first_name
, initcap(base26_encode(substring(random()::text,3,15)::bigint)) as last_name
, initcap(base26_encode(substring(random()::text,3,9)::bigint)) as patronymic
, 'now'::date - (interval '90 years' * random()) AS birth_date
, ceil(random()*2100000000) as photo_id
FROM generate_series(1,10) num;

select *
from person
order by person_id

person_id	first_name	last_name	patronymic	birth_date	photo_id
1	Cgdmyty	Bvmvzwflixr	Bxqseru	1975-04-30	735546878
2	Fnvbyvm	Gfcfcfqmqzf	Dddevru	1994-08-05	1001397097
3	Bcwpzckn	Dbotgnxawv	Ntvguj	1978-04-23	2061692939
4	Otxeaqq	Gsordusztt	Bydwdyn	2006-06-07	1237488926
5	Yvgsydx	Bsctpuizkfu	Dljgzg	1996-06-14	513060685
6	Bekfepnq	Nurheomrkd	Cesnzqr	1937-04-02	821299133
7	Tmofbgf	Cakqaxqkhw	Ccqdiav	2004-10-10	1112427504
8	Bgjaallw	Couazhvqmvp	Biavado	1935-05-05	1401285419
9	Bcnrcurk	Gblvvxkxbel	Csduzpr	2017-12-31	715217821
10	Bakcxfzn	Jfvmbyvawt	Bftbpyz	1985-01-12	1415422286

db<>fiddle here

Collectives™ on Stack Overflow

Generate random elements in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related