Postgresql – psql command output to print error via -L output.log

postgresql

wanted to know how to print error output via psql command
using below :

output of Insert_log.log shows only query statement not the error :

psql -h ${hostname} -p 5432 -U postgres -d ${DB_NAME} -a  -f ${DIR}/log.sql -L Insert_log.log

[ec2-user@scripts]$ ./log.sh 
\o
INSERT INTO mig.qb_tax_rates (id, xxx"}}', 18977);
psql:/home/ec2-user/scripts/log.sql:2: 
ERROR:  column "q_id" of relation "qb_tax_rates" does not exist
LINE 1: INSERT INTO mig.qb_tax_rates (id, q_id, sync_token, qb_updat...

[ec2-user@ip-10-99-99-999 scripts]$ cat Insert_log.log
********* QUERY **********
INSERT INTO mig.qb_tax_rates (id, xx}}', 18977);
**************************

Best Answer

The file specified to -L cannot be made to include errors.

To have errors in the same log file than queries and results, both outputs should be redirected to the same file. For example:

$ cat >script.sql
select 1/0; 

select count(*) from pg_class;

$ psql -e -f script.sql &> output.log

$ cat output.log 
select 1/0;
psql:script.sql:1: ERROR:  division by zero
select count(*) from pg_class;
 count 
-------
   352
(1 row)

See psql --help for more options:

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate
  -L, --log-file=FILENAME  send session log to file
  -n, --no-readline        disable enhanced command line editing (readline)
  -o, --output=FILENAME    send query results to file (or |pipe)
  -q, --quiet              run quietly (no messages, only query output)
  -s, --single-step        single-step mode (confirm each query)
  -S, --single-line        single-line mode (end of line terminates SQL command)
Related Question