Getting NOTICE on CREATE TABLE IF NOT EXISTS

Hi

I create tables in an update script. To avoid errors on further updates, the tables are created with IF NOT EXISTS like this:

CREATE TABLE IF NOT EXISTS nodes (
  NODEID INT NOT NULL,
  LASTACTIVITY TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (NODEID)
);

Sadly, I get these notices:
NOTICE: relation “nodes” already exists, skipping

What is the reason for that? I mean, I used IF NOT EXISTS to avoid such messages.

Can I stop that easily?

Can you help us understand the problem with the notices? Does it interfere in some negative way with a driver?

We added this behavior to be more user friendly and more postgres compatible. Below find the behavior of postgres:

postgres=# CREATE TABLE IF NOT EXISTS t (i INT PRIMARY KEY);
CREATE TABLE
postgres=# CREATE TABLE IF NOT EXISTS t (i INT PRIMARY KEY);
NOTICE:  relation "t" already exists, skipping
CREATE TABLE

Hi Andrew, I have an initialization script that is run by users. The output of the database script is printed to stdout. The first user who ran this script immediately came back to me, there are “problems in database creation”. It turned out it was that notice message that confused him :roll_eyes:

To be honest, I also do not expect such notice if I run IF NOT EXISTS. Neither MySQL, MariaDB, Oracle nor SQLite did such in the past.

In the past I used IF NOT EXISTS to avoid such messages and notices…

Can I turn that notice off using a SQL statement? I fear that more people will come back like this one.

You can disable these informational notices by changing the default message behavior (client_min_messages) from NOTICE to WARNING. You can set this variable in your client URL if you so choose.

SET client_min_messages TO WARNING;
1 Like