Conditionally drop temporary table in Redshift

Question

We are using http://aws.amazon.com/redshift/ and I am creating/dropping temporary tables in reports. Occasionally we encounter cases where someone has created a temporary table and failed to drop it.

In other databases, for instance PostgreSQL which Redshift is based on, I could simply:

DROP TEMP TABLE IF EXISTS tblfoo;

But that is a syntax error in Redshift. I can check for the existence of temporary tables myself using http://docs.aws.amazon.com/redshift/latest/dg/r_STV_TBL_PERM.html but that only works if I am a superuser and I am not running as a superuser. I could also go and swallow exceptions, but with my reporting framework I'd prefer not to go there.

So how can I, as a regular user and without generating database errors, conditionally drop a temporary table if it exists?

mike_pdb · Accepted Answer · 2014-07-07 19:59:58Z

2

+250

The test I ran showed that I could see other users' temp tables in stv_tbl_perm using a non-super user id. The cluster version I tested in is 1.0.797. Note that no users can see other users' temp tables in pg_class.

edited Jul 7, 2014 at 19:59

answered Jul 7, 2014 at 19:43

mike_pdb

2,82819 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

btilly Over a year ago

I swear, last week I got an error from select * from stv_tbl_perm where temp = 1; but now it seems to work. Did that just get changed in Redshift??

mike_pdb Over a year ago

Not sure. Glad it works for you now. Def would like the "if exists" functionality to get built in.

Collectives™ on Stack Overflow

Conditionally drop temporary table in Redshift

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related