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.

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

Solution 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

Solution 3

Use --quiet when you start psql.

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

Solution 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

Solution 5

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

View more solutions

Comments

[xenoterracide]

interesting... that shuts up all the stuff like CREATE TABLE but not the NOTICE's I'm not sure that I really think all of them are useless... but I do have a bit of a belief (and I think I read it in one of those must read C or Unix books) that programs should be quiet unless they have a problem or you tell them not to be. so --quiet should be the default and there should be a --verbose (perhaps with customizable levels)

[a_horse_with_no_name]

This can also be changed in postgresql.conf

[Steph]

Thank you for sharing this post, it was extremely helpful.

[KARASZI István]

Actually this answer is the same as @Gavin posted with just the relevant part.

[Jim Stewart]

It's generally frowned upon to answer with just a link, but I agree that this was a very informative blog post. Please consider extracting some of the key points and summarizing them in your answer, along with the link.

[F. Hauri - Give Up GitHub]

... Or just after connect by using some command like do or execute via any DBI interface.

[guettli]

NOTICE is still visible for me: SQL "alter table foo drop constraint if exists check_bar". Output: "NOTICE: constraint "check_bar" of relation "foo" does not exist, skipping"

[dimitarvp]

This removed the annoying "Pager usage is off." message for me so I adopted using -q in psql scripting from now on. Thank you.