Caching query result using temporary table

I want to cache result of big select query for further use. The idea is to use temporary table (I believe that it persists for session or at least for transaction):

CREATE TEMP TABLE cache AS (SELECT * FROM t)


but when cache is already created I encounter following error:

Invalid operation: relation "cache" already exists;


There are no IF NOT EXISTS condition for CREATE TEMP TABLE ... AS statement. So question is, is there some workaround for this case?

1 Answer
1

If you want to store just results for last "big query" in session then

Drop table if exists cache;
Create temp table cache as
select * from t where "your's conditions";


Dropping the cache table before will not block your creating table statement.

If you want to store results for all of your "big queries" then you will need some extra info in the naming of your cache.

You could check if temp table already exists in if statment

IF (select count(*)
from information_schema.tables
where table_name'cache'
and table_schema like 'pg_temp%')=0 THEN

create temp table cache as
select * from t where "your's conditions";
ELSE
select * from cache where "your's conditions";
END;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.