Suppressing "Notice: Relation exists" when using "CREATE ... IF NOT EXISTS"

Question

I have a function that creates a temporary table to store and process data. Problem is I want to run this function on the order of 1M times within a single transaction, without having:

NOTICE:  relation "foo" already exists, skipping

output ~1M times. Is there an efficient way to do so?

What is not efficient:

1. Dropping the table instead

   DROP TABLE IF EXISTS
   

   • Leads to running out of shared memory

2. Catching the duplicate_table exception (less efficient than using IF NOT EXISTS?)

   BEGIN
       CREATE TEMPORARY TABLE foo () ON COMMIT DROP;
   EXCEPTION
       WHEN duplicate_table THEN --do nothing
   END;
   

Answer 1

As others have pointed out, the client_min_messages setting is what you want. There are a number of ways to configure this.

SET client_min_messages = warning or SELECT set_config('client_min_messages', 'warning', false) will persist for the rest of the current session/connection.

SET LOCAL client_min_messages = warning or SELECT set_config('client_min_messages', 'warning', true) resets at the end of the current transaction.

The CREATE FUNCTION statement's SET clause will scope the setting only to this function; this sounds like the most suitable option in your case. For example:

CREATE FUNCTION f()
  RETURNS void
  SET client_min_messages = warning
  LANGUAGE plpgsql
  AS ...

Answer 2

Simply

SET client_min_messages = error;

before running the statement(s).

You can also set it on the psql command line for batch executions

PGOPTIONS="-c client_min_messages=error" psql -f somefile.sql dbname