CODEWITHSUNDEEP
amazon-redshift
ForeverConfused
ForeverConfused

Reputation: 1767

How do I reload a table without deleting existing views?

I have a dataset that spans across many tables by date.

table_name_YYYY_MM_DD

There are many VIEWS created across these date range tables. However whenever I need to reload a table, I have to delete all these views to remove dependency constraints

DROP TABLE IF EXISTS table_name_YYYY_MM_DD cascade;

Is there a way to reload the table, as part of a transaction, to where the VIEWS don't need to be deleted. Eg create a new table, and swap their names, so that the transaction would guarantee the views don't need to be deleted.

Upvotes: 2

Views: 253

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

Don't drop the table. Instead, truncate it:

 truncate table table_name_YYYY_MM_DD

This removes all rows (quickly), but the table remains. So other dependencies are not affected.

Afterwards, you need to insert the data back into the table, rather than recreating the table.

Upvotes: 4

Related Questions