>> INSERT INTO mytable (id, date, ...) SELECT id, NULLIF( date,
>> '0000-00-00' ), ... FROM mydump WHERE (FKs check and drop the borken
>> records);
>
> What do we gain against current way of doing it, which is:
> COPY loadtable FROM 'dump.txt' WITH ...
> INSERT INTO destination_table(...) SELECT ... FROM loadtable;
You read and write the data only once instead of twice (faster) if you
want to import all of it.If you just want to compute some aggregates and store the results in a
table, you just read the data once and don't write it at all.
The advantages are the same than your proposed transformations to COPY,
except I feel this way of doing it opens more options (like, you can
combine columns, check FKs at load, do queries on data without loading it,
don't necessarily have to insert the data in a table, don't have to invent
a new syntax to express the transformations, etc).