--we start by prepping the table for import. To do so, drop the id column that serves as a serial primary key
ALTER TABLE realtor_data DROP COLUMN if exists id;
-- then create a new temp table with 0 rows but exact schema
create table inventory_temp as select * from realtor_data limit 0;
--copy the data from the server
\copy inventory_temp   from /home/peter/realbloc/home_prices/RDC_Inventory_Core_Metrics_Zip.csv delimiter ',' csv header;
--do the rigamore with renaming tables
ALTER TABLE realtor_data RENAME TO table_old;
ALTER TABLE inventory_temp RENAME TO realtor_data;
DROP TABLE table_old;
--add primary key id
alter table realtor_data add column id serial primary key;


--------COUNTY LEVEL DATA---------

ALTER TABLE realtor_data_county DROP COLUMN if exists id;
CREATE TABLE inventory_temp_county as select * from realtor_data_county limit 0;
\copy inventory_temp_county from /home/peter/realbloc/home_prices/RDC_Inventory_Core_Metrics_County.csv delimiter ',' csv header;
ALTER TABLE realtor_data_county RENAME TO table_old_county;
ALTER TABLE inventory_temp_county RENAME TO realtor_data_county;
DROP TABLE table_old_county;
ALTER TABLE realtor_data_county add column id serial primary key;
update realtor_data_county set county_fips = '0'||county_fips where length(county_fips) = 4;
