0

When running a query that creates a temporary table if it doesn't exist, how do I determine if a new table was actually created?

For example if I create a temporary table with CREATE TEMP TABLE IF NOT EXISTS name can I get the query to return something if a new table was created or not?

In my specific case, upon creating a temporary table I then run another query to copy some data to it so I need to ensure that I am only copying the data if the temporary table was recreated (if for example the table was dropped because of some momentary connection drop out or something). I am using psycopg2.

Jonathan
  • 1,256
  • 4
  • 12
  • 18
  • 1
    Does this answer your question? [How to check if a table exists in a given schema](https://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema) – Ramon Medeiros Jan 28 '20 at 13:16
  • No it doesn't because I don't think that answer applies to temporary tables – Jonathan Jan 28 '20 at 13:24
  • eventually I used SELECT EXISTS ( SELECT 1 FROM information_schema.tables WHERE table_schema like 'pg_temp_%' AND table_name=LOWER('table_name') ) – Jonathan Jan 28 '20 at 13:54

1 Answers1

0

You should test if your table exists before you execute the query "CREATE TABLE IF NOT EXISTS". For example with this query :

    SELECT 1 
    FROM information_schema.tables 
    WHERE table_schema='YourShema' AND table_name='TableName';

If the table did not exist before, this means she has been created after ! ;)

An other solution is to not use the "IF NOT EXISTS" directive and catch the exception if table already exists.

fred727
  • 2,644
  • 1
  • 20
  • 16
  • I have edited the question to make it about temporary tables because this query cannot be used to to test if a temporary table exists (I tested it) – Jonathan Jan 28 '20 at 13:19