0

I am adding distkey/sortkey to all my tables in redshift and would like to automate this. I am doing the following:

ALTER TABLE table RENAME TO tmp_table;
CREATE TABLE table 
distkey(id) 
sortkey(id) 
AS 
select * from tmp_table;
DROP TABLE tmp_table;

This works great, except the views don't get migrated. When you ALTER TABLE, the existing views would point to the tmp_table. Ideally I want to restore the views to the way before, possibly in the same query transaction or as part of a script.

bugzpodder
  • 29
  • 1
  • 6
  • Be aware that CREATE TABLE .. AS does not keep the NOT NULL settings. All columns in the new table will be created as NULL. Also my guess is that column encoding/compression setting will not be maintained. – jhncz May 21 '16 at 15:24

2 Answers2

0

Just drop and recreate the views. That could be part of the script.

Views might not form part of a transaction, so some testing might be required.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • Thank your for your response. Can you elaborate how I can recreate another view if I just know the view name? eg CREATE TABLE AS example above you don't need to specify all the columns, is there a way to recreate the view without messing with and digging up what the actual query is? – bugzpodder May 19 '16 at 04:57
  • You could try connecting with `psql` and then see this: [How to see the CREATE VIEW code for a view in PostgreSQL?](https://stackoverflow.com/questions/14634322/how-to-see-the-create-view-code-for-a-view-in-postgresql) – John Rotenstein May 21 '16 at 23:17
0

In your migration procedure:

  1. For each view execute:

    select definition from pg_views where viewname = 'my_view';

  2. And store all the results.

  3. Execute for all views:

    drop view 'my_view'; // or rename only to have a rollback option

  4. Alter your tables

  5. Execute the create view commands obtained in step 2

jhncz
  • 163
  • 1
  • 8
  • I ended up with CREATE OR REPLACE VIEW instead of dropping the view and recreating it. Is there a way to replace select definition from pg_views where viewname = 'my_view'; with something that will find the name of all views that depends on a table? right now i am doing a select definition from pg_views where definition like '%table%'. I was looking at something that involves pg_depend class but didn't figure out. – bugzpodder May 23 '16 at 00:09
  • How would you do permissions and comments? pgAdmin III does a great job of this. – PhilHibbs Aug 01 '16 at 09:17