Perform an "upsert" from CSV file using Postgres copy command #sql #psql

bulk-upsert-from-temporary-table.sql

create temporary table temp (symbol varchar(255), open decimal, high decimal, low decimal, close decimal, volume varchar(255), date date );

create table if not exists stocks (id serial primary key, symbol varchar(255), open decimal, high decimal, low decimal, close decimal, volume varchar(255), date date, created_at timestamp, updated_at timestamp);

copy temp (symbol, date, open, high, low, close, volume) from '/path/to/file.csv' with delimiter ',' csv header;

delete from stocks using temp where stocks.date = temp.date and stocks.symbol = temp.symbol;

insert into stocks (symbol, open, high, low, close, volume, date) select symbol, open, high, low, close, volume, date from temp;

drop table temp;

deleting everything from a table is not an upsert

@pstanton Hence "upsert" in quotes in the description. However, it is much faster w/a lot of data to load csv, delete and insert compared to using a CTE.

Run the delete and insert in the same transaction, at least you wont have a race condition where someone reads an empty table until the inserts are complete. You make the upsert to avoid race conditions right ?