PostgreSQL Generate_Series() Insert Not Completing

Question

So I'm trying to skip generating mock data with an outside script and instead use generate_series() in PostgreSQL. If I do try less rows, at best it comes back with "could not write block: temporary log file...not enough space on device".

Code:

CREATE TABLE posts(
    id INTEGER PRIMARY KEY,
    author VARCHAR(20),
    likes INTEGER,
    content VARCHAR(200),
    posted TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO posts
SELECT DISTINCT id, author, likes, content, posted FROM 
    generate_series(1,10000) AS id, substr(md5(random()::text), 0, 20) AS 
    author, generate_series(1,10000) AS likes, md5(random()::text) AS 
    content, generate_series('2007-02-01'::timestamp, 
    '2018-04-01'::timestamp, '1 hour') AS posted;

A few possibilities I could think of:

• This is somehow causing a branching factor, in which case there may be a more efficient way to write it
• My hardware is insufficient (i5-4210U, 8GB RAM, 500GB HDD with about 20GB left of space). But I've also run this on my 2TB desktop to the same results.
• The md5 hash or random() functions are causing a huge blockage, which is why my computer freezes for the first few minutes of running this query.

Answer 1

By doing what you do in the from clause you get a cartesian product of all the sets you generate. If you just want to generate 10000 rows something like the following is what you want.

INSERT INTO posts
SELECT id, substr(md5(random()::text), 0, 20) AS author, (random() * 100)::integer AS likes, 
    md5(random()::text) AS content, '2007-02-01'::timestamp + (id * '1 hour'::interval) AS posted 
FROM 
    generate_series(1,10000) AS id