How to access the "notice" value from Postgresql when using `create table if not exists`

Question

I have the statement create table if not exists so that my code does not try to create the table over and over. I would like to print a message of "Table already created" if the table already exists but I don't know how to access the "notice" spoken of in the documentation so I can use it in a conditional statement.

From documentation:

IF NOT EXISTS
Do not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the one that would have been created.

It's not clear what do you want. When you're trying to re-create a table with `IF NOT EXISTS` the notice is actually being raised, and table is not being created. What else do you need? — ginkul, Aug 06 '21 at 20:00
I am using Python3. @ginkul, I would like to produce a message that prints to the console that the table already exists. — Peter Anderson, Aug 23 '21 at 20:01

score 0 · Answer 1 · answered Aug 06 '21 at 23:27

To raise your own NOTICE / WARNING / EXCEPTION, take a different approach. Like:

DO
$do$
BEGIN
   IF EXISTS (SELECT FROM pg_catalog.pg_tables 
              WHERE  schemaname = 'myschema'
              AND    tablename  = 'mytable') THEN
      RAISE NOTICE 'Table myschema.mytable already created.';
   ELSE
      CREATE TABLE myschema.mytable (i integer);  -- your table definition here
   END IF;
END
$do$;

See:

PostgreSQL create table if not exists

How to access the "notice" value from Postgresql when using `create table if not exists`

1 Answers1