I have this little conundrum going on: I want to bulk import historical data from multiple CSV files like
binance_btc_usd.csv with structure of
time|value into table
id refers to the identification of what exchange and what currency pair is going on, which is the file name. I dont want to bake-in an ID into the CSV files, as they may not be valid next time I re-run the script. COPY doesn't seem like the thing I am looking for.
CREATE TABLE history_data ( id SERIAL PRIMARY KEY, pair INTEGER NOT NULL, time TIMESTAMP, value DOUBLE PRECISION, CONSTRAINT pair_pair_id_fk FOREIGN KEY (pair) REFERENCES pair (id) ON DELETE CASCADE ); CREATE TABLE pair ( id SERIAL PRIMARY KEY, exchange INTEGER NOT NULL, base_coin INTEGER NOT NULL, quoted_coin INTEGER NOT NULL # and other ... CONSTRAINT exchange_currency_id_fk FOREIGN KEY (exchange) REFERENCES exchange (id) ON DELETE CASCADE CONSTRAINT base_coin_id_fk FOREIGN KEY (base_coin) REFERENCES currency (id) ON DELETE CASCADE CONSTRAINT quoted_coin_id_fk FOREIGN KEY (quoted_coin) REFERENCES (id) ON DELETE CASCADE CONSTRAINT pair_pair_id_fk FOREIGN KEY (pair) REFERENCES pair (id) ON DELETE CASCADE );