postgresql add tag
Jack Douglas (imported from SE)
I would like to be able to generate random `bytea` fields of arbitrary length (<1Gb) for populating test data.

What is the best way of doing this?
Top Answer
craig ringer (imported from SE)
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);
    LANGUAGE 'sql'
    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
Answer #2
Jack Douglas (imported from SE)
> I would like to be able to generate random bytea fields of arbitrary length

This function will do it, but 1Gb will take a long time because it does not scale linearly with output length:


This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.