Database Reference
In-Depth Information
or even using a random ordering
postgres=# SELECT generate_series(1,10) as key
,(random()*100.)::numeric(4,2)
,repeat('1',(random()*25)::integer)
ORDER BY random();
key | numeric | repeat
-----+---------+-------------------------
6 | 70.31 | 11111111111111111111111
4 | 2.37 | 111111111111111111
1 | 76.99 | 11111111111111
8 | 35.90 | 1111111111111
3 | 59.21 | 111111111
2 | 88.86 | 11111111
7 | 67.32 | 111111
9 | 15.66 | 111111
5 | 79.90 | 11111
10 | 25.09 | 1
(10 rows)
How it works...
Set returning functions literally return a set of rows. That allows them to be used in either the
FROM
clause, as if they were a table, or in the
SELECT
clause. The
generate_series()
set of functions return either dates or integers, depending upon the datatypes of the input
parameters you use.
The
::
operator is used to cast between datatypes.
The "random string from a list of strings" example uses PostgreSQL arrays. You can create an
array using the ARRAY constructor syntax, and then use an integer to reference one element
in the array. In our case, we used a random subscript.
There's more...
There are also some commercial tools to generate application-specific test data for
PostgreSQL. Both of the tools listed here are under $250 per copy. Not too much money,
so OK to mention in an open source software topic!
http://www.sqlmanager.net/products/postgresql/datagenerator
http://www.datanamic.com/datagenerator/index.html
Key features for any data generator would be as follows:
