How do we handle timestamp formatting errors on large data imports?
We figured it out. 

Some of our timestamp text data was imported in scientific notation (i.e. 2.0160924 E+13. This threw an error, as we didn't include the . in the template format in the to_datetime() function. 

Our solution was to pull the datetime from another column that contained more or less duplicate data.

Point is - the to_datetime() function in PostgreSQL is very particular and requires data going in to match the format you define. If you're having a similar issue run a few tests to figure out where your data isn't matching up to what the function expects.

We also saw that Postgres MAY support a conversion from scientific notation. We couldn't find anything in 9.3 that did, but it seems like it is on their list of things to implement.