Search code examples
postgresqlcsverror-handlingbulk-load

Postgres Copy command with Log redirection

I am using Postgres Copy utility to load the data to Postgres table from CSV file. Currently using the below command

psql -h 127.0.0.1 -d target -U postgres -c "\copy TableName FROM 'E:\Dev\XXX_1_0.csv'  delimiter '^'" -o E:/Dev/XXX.log

When there is an issue in the data, error information are not getting updated in the log file. Whereas when there is no error, my log files is updated with loaded row count. fo example (COPY 25)

I tried to execute the above command from command prompt & below error is reported. Let me know how to get the error information or redirect the errors to log files for the reference.

ERROR:  value too long for type character varying(255)
CONTEXT:  COPY TableName, line 2, column Name: "NickName..."
Solution

  • I don't know of a way to redirect the error output directly in psql. You can get your shell to do it for you.

    This works to combine both stdout and stderr into one file named "log". It works both in bash and in Windows CMD:

    psql -c "whatever" > log 2>&1