I am working on a windows application.
I want to import a .csv file into postgresql table. For this purpose I have tried COPY command. This command loaded the file with some errors. It has skipped some row due to incompatible data types. I want to log these incompatible rows into some log file.
Is there a way to log skipped/invalid rows into some log file using COPY command.
What are the other options available in postgressql for bulk insert that will import data and also log errors into some log file.
I don't know what facilities Windows has for tasks like this, but we scrub CSV output with sed to check for type/row compliance and insert a NULL marker if appropriate before feeding them to Postgres COPY. Then we don't have errors to log.
In more complex cases where the CSV doesn't line up with the schema (very common to have non-normalized CSV output) we write an updatable view in the database (using re-write rules -- triggers are slow on huge COPY operations involving millions of rows and multiple tables) and then use an awk script to reform the CSV to fit the view exactly. Any completely "bad" columns can be stripped out by the awk script and placed in a separate "bad rows" report.
But again, on Windows I have no idea. Its pretty weak on built-in text processing functionality, which is precisely the thing you need when working between non-conformant text formats like CSV and a database.