In recent versions of postgreSQL one can use the syntax:

create temp table if not exist my_table as 

to create a temporary table if it doesn't exist yet.

However, the clause if not exist is a relatively recent feature.

I am working against a database that uses PostgreSQL version 8.2.15. How can I create a temporary table if it doesn't exist yet?

  • Upgrade? Seriously, 8.2.15? – Craig Ringer Feb 11 '14 at 0:24

The actual solution very much depends on your exact situation. If you can use a pl/pgsql function, you can check first for the existence of the table:

IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'my_table')

You could also try to match the schema name (which will be something like pg_temp*) by joining the pg_namespace table to pg_class. Or use the pg_tables view, which can be a bit easier.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.