CODEWITHSUNDEEP
postgresqlgenerate-series
A13X
A13X

Reputation: 436

PostgreSQL Generate_Series() Insert Not Completing

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:

Upvotes: 3

Views: 1635

Answers (1)

Eelke
Eelke

Reputation: 22033

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

Upvotes: 3

Related Questions