0

How to generate random data for any table on pgsql in postgresql CREATE OR REPLACE FUNCTION service.generate_random(sschema_name text, stable_name text, row_count int)

0

1 Answer 1

2
    CREATE OR REPLACE FUNCTION service.generate_random(sschema_name text, stable_name text, row_count int)
    RETURNS void
    LANGUAGE plpgsql
    VOLATILE
AS $$
    
declare
 rec RECORD;
 max_len int;
 ssql text;
 res_sql  text default '';
 nmax_len int;
 columns text [];
 sels text [];
 a int default 0;
begin
     for rec in 
        select column_name, data_type from information_schema.columns
        where table_name = stable_name
        and table_schema = sschema_name
        and column_default is null
        order by ordinal_position
     loop
      --raise notice 'colum = % data_type=%', rec.column_name, rec.data_type;
      if rec.data_type= 'text' then
        select floor(random() * (50-1+1) + 1)::int into nmax_len;
        ssql = 'service.random_string('||nmax_len::text||') as '||rec.column_name;
      elsif rec.data_type= 'uuid' then
        select floor(random() * (50-1+1) + 1)::int into nmax_len;
        ssql = 'md5(service.random_string('||nmax_len::text||'))::uuid as '||rec.column_name;
      elsif rec.data_type= 'boolean' then
       ssql = 'RANDOM()::INT::BOOLEAN as '||rec.column_name;       
      elsif rec.data_type= 'date' then
       ssql = '(date ''1956-01-01'' + random() * (now()-date ''1956-01-01 20:00:00''))::date as '||rec.column_name;
      elsif rec.data_type= 'timestamp without time zone' then
       ssql = 'timestamp ''epoch'' + (
          extract(''epoch'' from timestamp ''1956-01-01 00:00:00'')
        + random() * (extract(''epoch'' from now())  - extract(''epoch'' from timestamp ''1956-01-01 00:00:00''))
                ) * interval ''1 second'' as '||rec.column_name;      
      elsif rec.data_type in ('bigint','int8') then
       select floor(random() * (19-1+1) + 1)::int into nmax_len;
       ssql = 'floor(random() * ('||nmax_len||'-1+1) + 1)::'||rec.data_type||' as '||rec.column_name;
      elsif rec.data_type in ('integer','int4','int') then
       select floor(random() * (10-1+1) + 1)::int into nmax_len;
       ssql = 'floor(random() * ('||nmax_len||'-1+1) + 1)::'||rec.data_type||' as '||rec.column_name;
      end if;       
      --raise notice 'colum = % ok', rec.column_name;
      select array_append(sels, ssql) into sels;      
      select array_append(columns, rec.column_name::text) into columns;
      
     end loop;
     res_sql:='insert into '||sschema_name||'.'||stable_name||'('||array_to_string(columns,',')||') select '||array_to_string(sels,',')||';';     
     raise notice '%',res_sql;
     for ii in 1..row_count loop
         execute res_sql;
     end loop;   
     
end;    
$$
EXECUTE ON ANY;
Sign up to request clarification or add additional context in comments.

1 Comment

19, 50,10 - random words length. You can replace it with variables. 1956-01-01 - begin random date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.