PostgreSQL – How to Generate Sets of Random Integers

postgresqlpostgresql-9.3random

With this command I can generate 16384 random integers between 1 AND 200,000.

SELECT generate_series (1,16384),(random()*200000)::int AS id

I want to generate 10 sets of such integers. Each set must have an integer identifier, something like that:

1 | 135

1 | 1023

… end of first set of 16384 random numbers

2 | 15672

2 | 258732

… end of second set of 16384 random numbers

Is this possible with an SQL command, or should I write a function for that?

Best Answer

Would this be what you want?

SELECT 
   n, generate_series (1,16384), (random()*200000)::int AS id 
FROM  
   generate_series (1,10) AS x(n) ;

or the similar:

SELECT 
    n, i, (random()*200000)::int AS id 
FROM  
    generate_series (1,10) AS x(n) 
  CROSS JOIN
    generate_series (1,16384) AS y(i) ;

Related Solutions

Postgresql – Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

How about using with recursive

test view:

create view v as 
select *
from ( values ('foo', 2),
              ('foo', 3),
              ('foo', 4),
              ('foo', 10),
              ('foo', 11),
              ('foo', 13),
              ('bar', 1),
              ('bar', 2),
              ('bar', 3)
     ) as baz ("name", "int");

query:

with recursive t("name", "int") as ( select "name", "int", 1 as span from v
                                     union all
                                     select "name", v."int", t.span+1 as span
                                     from v join t using ("name")
                                     where v."int"=t."int"+1 )
select "name", "start", "start"+span-1 as "end", span
from( select "name", ("int"-span+1) as "start", max(span) as span
      from ( select "name", "int", max(span) as span 
             from t
             group by "name", "int" ) z
      group by "name", ("int"-span+1) ) z;

result:

 name | start | end | span
------+-------+-----+------
 foo  |     2 |   4 |    3
 foo  |    13 |  13 |    1
 bar  |     1 |   3 |    3
 foo  |    10 |  11 |    2
(4 rows)

I'd be interested to know how that performs on your billion row table.

PostgreSQL Random Bytea – How to Generate a Random Bytea

Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use:

CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
    SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
    FROM generate_series(1, $1);
$body$
LANGUAGE 'sql'
VOLATILE
SET search_path = 'pg_catalog';

It's a simple SQL function that's cheaper to call than PL/PgSQL.

The difference in performance due to the changed aggregation method is immense for larger bytea values. Though the original function is actually up to 3x faster for sizes < 50 bytes, this one scales much better for larger values.

Or use a C extension function:

I've implemented a random bytea generator as a simple C extension function. It's in my scrapcode repository on GitHub. See the README there.

It nukes the performance of the above SQL version:

regress=# \a
regress=# \o /dev/null
regress=# \timing on
regress=# select random_bytea(2000000);
Time: 895.972 ms
regress=# drop function random_bytea(integer);
regress=# create extension random_bytea;
regress=# select random_bytea(2000000);
Time: 24.126 ms

Best Answer

Related Solutions

Postgresql – Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

PostgreSQL Random Bytea – How to Generate a Random Bytea

Related Question