RE: Load data from a csv file without using COPY

From: Ravi Krishna [mailto:srkrishna(at)yahoo(dot)com]
Sent: Tuesday, June 19, 2018 4:15 PM
To: Steve Atkins <steve(at)blighty(dot)com>
Cc: PG mailing List <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Load data from a csv file without using COPY

>
> If performance is relevant then your app should probably be using COPY
> protocol, not line by line inserts. It's supported by most postgresql
> access libraries. If your app does that then using "\copy" from psql would be an appropriate benchmark.

Actually the reluctance to not use COPY is to make the benchmark same across two different RDBMS in two diff env.

---

Seems like a ~10 line Perl program could handle this very easily. Use the Text::CSV module to make handling of the input easier. Prepare your insert statement, then once you've pulled each line of input in execute the insert. For slightly better performance, I'd probably use transactions, add a counter, and commit every 10,000 rows (or something like that).

Once you have that working with 1 DB, you can copy your program, change the DBD driver and the connect statement to the other DB and try the other one. Unless you want to be really clever and make the same program do both and pick the DB by a command line switch. :)

HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.