3

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?

Gatis Seja
  • 87
  • 2
  • 2
  • 7
  • I know nothing about redshift, but I would look at try catch end catch in SQL... – LJ01 Jul 10 '18 at 10:01
  • 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! – John Rotenstein Jul 10 '18 at 12:31

1 Answers1

6

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.

Amith Kumar
  • 4,400
  • 1
  • 21
  • 28