Skip to content

Instantly share code, notes, and snippets.

@seanbehan
Created October 17, 2015 16:49
Show Gist options
  • Save seanbehan/d8d0697d3dbe7eb90550 to your computer and use it in GitHub Desktop.
Save seanbehan/d8d0697d3dbe7eb90550 to your computer and use it in GitHub Desktop.
Perform an "upsert" from CSV file using Postgres copy command #sql #psql
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;
@karthikcru
Copy link

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 ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment