disable NOTICES in psql output

Database Problem Overview

How do I stop psql (PostgreSQL client) from outputting notices? e.g.

> psql:schema/auth.sql:20: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"

In my opinion a program should be silent unless it has an error, or some other reason to output stuff.

Database Solutions

Solution 1 - Database

SET client_min_messages TO WARNING;

That could be set only for the session or made persistent with ALTER ROLE or ALTER DATABASE.

Or you could put that in your ".psqlrc".

Solution 2 - Database

Probably the most comprehensive explanation is on Peter Eisentrauts blog entry here (Archive)

I would strongly encourage that the original blog be studied and digested but the final recommendation is something like :

PGOPTIONS='--client-min-messages=warning' psql -X -q -a -1 -v ON_ERROR_STOP=1 --pset pager=off -d mydb -f script.sql

Solution 3 - Database

Use --quiet when you start psql.

A notice is not useless, but that's my point of view.

Solution 4 - Database

It can be set in the global postgresql.conf file as well with modifiying the client_min_messages parameter.

Example:

client_min_messages = warning

Solution 5 - Database

I tried the various solutions suggested (and permutations thereof) suggested in this thread, but I was unable to completely suppress PSQL output / notifications.

I am executing a claws2postgres.sh BASH script that does some preliminary processing then calls/executes a PSQL .sql script, to insert 1000's of entries into PostgreSQL.

...
PGOPTIONS="-c client_min_messages=error"
psql -d claws_db -f claws2postgres.sql

Output

[victoria@victoria bash]$ ./claws2postgres.sh
 pg_terminate_backend 
----------------------

DROP DATABASE
CREATE DATABASE
You are now connected to database "claws_db" as user "victoria".
CREATE TABLE
SELECT 1
INSERT 0 1
UPDATE 1
UPDATE 1
UPDATE 1
Dropping tmp_table
DROP TABLE

You are now connected to database "claws_db" as user "victoria".
psql:/mnt/Vancouver/projects/ie/claws/src/sql/claws2postgres.sql:33: NOTICE:  42P07: relation "claws_table" already exists, skipping
LOCATION:  transformCreateStmt, parse_utilcmd.c:206
CREATE TABLE
SELECT 1
INSERT 0 1
UPDATE 2
UPDATE 2
UPDATE 2
Dropping tmp_table
DROP TABLE

[ ... snip ... ]

SOLUTION

Note this modified PSQL line, where I redirect the psql output:

psql -d claws_db -f $SRC_DIR/sql/claws2postgres.sql &>> /tmp/pg_output.txt

The &>> /tmp/pg_output.txt redirect appends all output to an output file, that can also serve as a log file.

BASH terminal output

[victoria@victoria bash]$ time ./claws2postgres.sh
 pg_terminate_backend 
----------------------

DROP DATABASE
CREATE DATABASE
2:40:54                       ## 2 h 41 min
[victoria@victoria bash]$

Monitor progress:

In another terminal, execute

PID=$(pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }'); while kill -0 $PID >/dev/null 2>&1; do NOW=$(date); progress=$(cat /tmp/pg_output.txt | wc -l);  printf "\t%s: %i lines\n" "$NOW" $progress; sleep 60; done; for i in seq{1..5}; do aplay 2>/dev/null /mnt/Vancouver/programming/scripts/phaser.wav && sleep 0.5; done
...
Sun 28 Apr 2019 08:18:43 PM PDT: 99263 lines
Sun 28 Apr 2019 08:19:43 PM PDT: 99391 lines
Sun 28 Apr 2019 08:20:43 PM PDT: 99537 lines
[victoria@victoria output]$

pgrep -l -f claws2postgres.sh | grep claws | awk '{ print $1 }' gets the script PID, assigned to $PID
while kill -0 $PID >/dev/null 2>&1; do ... : while that script is running, do ...
cat /tmp/pg_output.txt | wc -l : use the output file line count as a progress indicator
when done, notify by playing phaser.wav 5 times
phaser.wav: https://persagen.com/files/misc/phaser.wav

Output file:

[victoria@victoria ~]$ head -n22 /tmp/pg_output.txt
You are now connected to database "claws_db" as user "victoria".
CREATE TABLE
SELECT 1
INSERT 0 1
UPDATE 1
UPDATE 1
UPDATE 1
Dropping tmp_table
DROP TABLE

You are now connected to database "claws_db" as user "victoria".
psql:/mnt/Vancouver/projects/ie/claws/src/sql/claws2postgres.sql:33: NOTICE:  42P07: relation "claws_table" already exists, skipping
LOCATION:  transformCreateStmt, parse_utilcmd.c:206
CREATE TABLE
SELECT 1
INSERT 0 1
UPDATE 2
UPDATE 2
UPDATE 2
Dropping tmp_table
DROP TABLE

References

[re: solution, above] https://stackoverflow.com/questions/35482382/psql-how-can-i-prevent-any-output-on-the-command-line
[re: this SO thread] https://stackoverflow.com/questions/3530767/disable-notices-in-psql-output?noredirect=1&lq=1
[related SO thread] https://stackoverflow.com/questions/21777564/postgresql-is-there-a-way-to-disable-the-display-of-insert-statements-when-rea
[relevant to solution] https://askubuntu.com/questions/350208/what-does-2-dev-null-mean

The > operator redirects the output usually to a file but it can be to a device. You can also use >> to append.
If you don't specify a number then the standard output stream is assumed but you can also redirect errors

  > file redirects stdout to file
  1> file redirects stdout to file
  2> file redirects stderr to file
  &> file redirects stdout and stderr to file

/dev/null is the null device it takes any input you want and throws it away. It can be used to suppress any output.

Solution 6 - Database

Offering a suggestion that is useful for a specific scenario I had:

Windows command shell calls psql.exe call to execute one essential SQL command
Only want to see warnings or errors, and suppress NOTICES