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 ??!!
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);
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.