In PostgreSQL I previously created a table like so:
CREATE TABLE IF NOT EXISTS stock_data ( code varchar, date date, open decimal, high decimal, low decimal, close decimal, volume decimal, UNIQUE (code, date) );
The idea is to import multiple csv files into this table. My approach is to use COPY … FROM STDIN instead of COPY … FROM ‘/path/to/file’, as I want to be able to cat
from the shell multiple csv files and pipe them to the sql script. The sql script to accomplish this currently looks like this:
CREATE TEMPORARY TABLE IF NOT EXISTS stock_data_tmp ( code varchar, ddate varchar, open decimal, high decimal, low decimal, close decimal, volume decimal, UNIQUE (code, ddate) ); copy stock_data_tmp FROM STDIN WITH CSV; INSERT INTO stock_data SELECT code, to_date(date, 'YYYYMMDD'), open, high, low, close, volume FROM stock_data_tmp; DROP TABLE stock_data_tmp;
An example csv file looks like this
AAA,20140102,21.195,21.24,1.16,1.215,607639 BBB,20140102,23.29,2.29,2.17,2.26,1863 CCC,20140102,61.34,0.345,0.34,0.34,112700 DDD,20140102,509.1,50.11,50.09,50.11,409863
From the shell I try:
cat /path/to/20140102.txt | psql -d my_db_name -f ~/path/to/script/update_stock_data.sql
But it gives me this error:
psql:/path/to/script/update_stock_data.sql:22: ERROR: missing data for column "date" CONTEXT: COPY stock_data_tmp, line 1: ""
However, if in my script I change the COPY command to:
copy stock_data_tmp FROM '/path/to/20140102.txt' WITH csv;
… and simply call
psql -d my_db_name -f ~/path/to/script/update_stock_data.sql
it succeeds.
Why am I getting this error when using cat
and STDIN
, and not when using the file PATH?
Advertisement
Answer
Because if you use -f
, COPY
will try to read from that file and not from stdin.