Redshift: TRUNCATE TABLE IF EXISTS

Question

It is recommended to use TRUNCATE TABLE instead of DELETE. However, truncate table does not support the IF EXISTS clause. The alternative is to DROP TABLE and recreate but needs DDL. Is there a way to do TRUNCATE TABLE if only the table exists?

Solution

You have two options to achieve it:

SQL Procedure/Script
Using IF condition, checking if table exists, then only truncate your table.

With Plain SQL Statements
Use Create table with if not exists in combination with Truncate, this will ensure table always exists & your consecutive SQL statements don't error out & stop.

CREATE TABLE @tobetruncated IF NOT EXISTS
TRUNCATE TABLE @tobetruncated

NOTE: This is not specific to REDSHFIT, mostly applies to all DB unless it supports special functions (like one I know Oracle has TABLE_EXISTS_ACTION). Truncate is like a all or nothing operation, and thats what makes it much better in performance than DELETE.

Comments

[LJ01]

I know nothing about redshift, but I would look at try catch end catch in SQL...

[John Rotenstein]

Do you have any problem with just running TRUNCATE, even if it generates an error? Or will that stop a script from running? Also, why do you think the table might not exist? If it doesn't exist, you can't do anything with it anyway, so your script would fail too!