update_table.sql

-- ABOUT -----------------------------------------------------------------------
-- This script is meant to outline a procedure for updating a PostgreSQL table
-- with the latest features even if there are views that depend on the table.
-- In other words, this method allows you to update the table without deleting
-- it and recreating it with the latest data. There are definitely some
-- opportunities for improvement - there may be a bit too much redundancy - but
-- this should get the job done, especially on datasets where size is trivial.
--------------------------------------------------------------------------------

-- NOTES -----------------------------------------------------------------------
-- This example uses update_table as a stand-in for whatever table you're
-- looking to update.
--------------------------------------------------------------------------------

-- STEPS -----------------------------------------------------------------------
-- Create a backup of the table you're going to update
CREATE TABLE update_table_backup AS TABLE update_table;

-- Create an empty table based on the schema of the table you're updating
CREATE TABLE update_table_updates AS TABLE update_table WITH NO DATA;

-- Import data in the empty table. I'm mostly using QGIS so I would probably do
-- this via DB Manager but you could do it with ogr2ogr as well
-- >> ogr2ogr -append -f "PostgreSQL" PG:"dbname=db password=password host=localhost port=5432" update_data.geojson -nln update_table_updates

-- Remove all records from table being updated
TRUNCATE TABLE update_table;

-- Copy new records into table you're updating
INSERT INTO update_table SELECT * FROM update_table_updates;

-- If the update worked, drop the updates and backup tables
DROP TABLE update_table_updates;
DROP TABLE update_table_backup;
--------------------------------------------------------------------------------