Skip to content

Instantly share code, notes, and snippets.

Last active Sep 30, 2019
What would you like to do?
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment