Jack Douglas
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](https://github.com/ringerc/scrapcode/tree/master/postgresql/random_bytea). 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
> 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:

How can I generate a random bytea

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.