CODEWITHSUNDEEP
postgresqlinsertauto-increment
sten
sten

Reputation: 7476

Fastest way to create a million records in Postgresql?

what is the fastest way to create million records, where the first column is a incremental number (not a primary key) and the other fields are with their default values ?

Do I have to create a PG procedure to be faster, because it is internal i.e. does not go trough DB driver ??!!

Upvotes: 5

Views: 3551

Answers (2)

jjanes
jjanes

Reputation: 44192

I would use insert into...select...from generate_series(1,1e6) because it is the easiest and speed is not everything, but it is not the fastest.

insert into foobar (id,id2) select x,x from generate_series(1,1e6) f(x);
INSERT 0 1000000
Time: 4346.187 ms (00:04.346)

copy foobar (id,id2) from program 'perl -le "print qq{\$_\t\$_} foreach 1..1_000_000"';
COPY 1000000
Time: 2720.630 ms (00:02.721)

Who knows, there might be something faster yet.

Upvotes: 2

JGH
JGH

Reputation: 17846

You can use generate_series and omit all columns that should use the default:

insert into test(id) select generate_series(1,1000000);

To insert the same sequence into multiple columns, you can reuse the series:

insert into test(id,id2) 
select seq,seq 
from generate_series(100,110) v(seq);

Upvotes: 8

Related Questions