PostgreSQL COPY FROM STDIN not working, but FROM PATH works

Question

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?

Laurenz Albe · Accepted Answer · 2018-01-22 12:16:21Z

7

Because if you use -f, COPY will try to read from that file and not from stdin.

answered Jan 22, 2018 at 12:16

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

meyer1994 Over a year ago

Where did you get his information? Is it documented somewhere?

Laurenz Albe Over a year ago

@meyer1994 Probably not in that detail, but you can figure it out easily by trial and error or by reading the source.

meyer1994 Over a year ago

Yes, yes. I tested as soon as I saw your command and it worked. I just wanted to know where did you find out about it. Trial and error?

Laurenz Albe Over a year ago

@meyer1994 Yes, if memory serves.

Collectives™ on Stack Overflow

PostgreSQL COPY FROM STDIN not working, but FROM PATH works

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related