Redshift: TRUNCATE TABLE IF EXISTS

sql amazon-redshift truncate drop-table
Gatis Seja picture Gatis Seja · Jul 10, 2018 · Viewed 11.5k times · Source

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?

Answer

Amith Kumar picture Amith Kumar · Jul 10, 2018

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.

Related questions

postgresql - view schema privileges

Is there a query I can run to show currently assigned privileges on a particular schema? i.e. privileges that were assigned like so: GRANT USAGE ON SCHEMA dbo TO MyUser I have tried SELECT * FROM information_schema.usage_privileges; …

Read More
How do I view grants on Redshift

I'd like to view grants on redshifts. I found this view for postgres: CREATE OR REPLACE VIEW view_all_grants AS SELECT use.usename as subject, nsp.nspname as namespace, c.relname as item, c.relkind as type, use2.usename …

Read More
How to get the last day of month in postgres?

How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using to_date("act_dt",'YYYYMMDD') AS "act date" …

Read More