4 ms·
With postgres, simple views are updatable, so you can often do this pattern without copying tables or dual writes. It's particularly useful for renaming columns
by singron 3y ago
With postgres, simple views are updatable, so you can often do this pattern without copying tables or dual writes. It's particularly useful for renaming columns, but you can also do some other things. You also don't have to use version numbers on all your tables since you only need the view temporarily.
ALTER TABLE my_foo RENAME TO my_foo_tmp;
ALTER TABLE my_foo_tmp <your desired change here>;
CREATE VIEW my_foo AS SELECT <how you make your new table seem like your old table>;
COMMIT;
<update your apps to use my_foo_tmp>
DROP VIEW my_foo;
ALTER TABLE my_foo_tmp RENAME TO my_foo;
CREATE VIEW my_foo_tmp as select * from my_foo;
COMMIT;
<update your apps to use my_foo>
DROP VIEW my_foo_tmp; COMMIT;
- munk-a 3y agoWe actually used a similar tactic when transitioning our data to supporting soft deletion. We have a limited number of queries that update data or present data for administrative review and processing - but a lot of wild ones around reporting. We'd rename table `foo` to `foowithdeleted` and create a new `foo` view that excludes any soft-deleted rows. Our reporting queries keep on trucking like normal and read out of the `foo` table (now a view - but they don't care) and we only need to adjust the administrative view to show soft-deleted rows for undeletion operations. Shell-gaming tables with views can be incredibly useful in postgres.