disable NOTICES in psql output

Question

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.

Answer 1

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".

Answer 2

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

Answer 3

Use --quiet when you start psql.

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

Answer 4

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

Example:

client_min_messages = warning

Answer 5

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] PSQL: How can I prevent any output on the command line?
• [re: this SO thread] disable NOTICES in psql output

• [related SO thread] Postgresql - is there a way to disable the display of INSERT statements when reading in from a file?

• [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.

Answer 6

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

Example:

psql.exe -c "SET client_min_messages TO WARNING; DROP TABLE IF EXISTS mytab CASCADE"

(I was unable to make things work with PGOPTIONS as a Windows environment variable--couldn't work out the right syntax. Tried multiple approaches from different posts.)