While reproducing an example from ClickHouse’s blog, I needed to insert some data provided as SQL with INSERT statements.
INSERT is the standard transactional way to insert data into a database. There’s nothing wrong with it. But when you’re trying to insert data into a PostgreSQL database in bulk, there’s a better way: COPY FROM.
When using COPY, PostgreSQL itself will read and parse a file full of row data and insert it into a table. There is very little overhead of parsing SQL, etc. This is the format used by pg_dump by default when producing an SQL dump output. But, to use it, the input data must be in the right format. PostgreSQL explains the format it expects in its documentation. It’s basically TSV with a couple of provisions for escaping values.
On the other hand, the input we’ve got is an SQL file looking like this: (more…)