Wednesday, March 14, 2012

PostgreSQL - Generating fake data with Common Table Expressions

I'm filling an orders table with sample data for a sql course. I have two tables: employee (the one who placed the order) and distributor, the task to generate 1,000,000 orders between 2005 and 2012.

WITH RECURSIVE source (counter) AS ( SELECT 1 UNION ALL SELECT counter +1 FROM source where counter < 1000000)
(random() * (select max(employee_id) - min(employee_id) from wcm.employee))::int + (select min(employee_id) from wcm.employee) employee_id,
(random() * (select max(distributor_id) - min(distributor_id) from wcm.distributor))::int + (select min(distributor_id) from wcm.distributor) distributor_id,
( '2005-01-01'::date + ((now()::date - '2005-01-01'::date)::int * random())::int ) order_date
FROM source;

I love CTE's !!!! Don't you?

No comments:

Post a Comment