current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
 
Upgrade? Seriously, 8.2.15? –  Craig Ringer Feb 11 at 0:24
add comment

1 Answer

up vote 2 down vote

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')
THEN
    CREATE TEMPORARY TABLE my_table ...
END IF;

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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