Copy file from CSV into Postgresql table - timestamp problem

Question

I have data in csv format with rows of daily company stock quotes which look like this:

INTSW2027243,20200319,7.7700,7.7800,7.3600,7.3600,2442
INTSW2027391,20200319,7.4200,7.6000,6.8300,6.8900,15262
INTSW2027409,20200319,7.4800,7.5600,7.4200,7.5600,743
INTSW2028365,20200319,0.7100,0.7200,0.5400,0.5500,47495 
Atari,20200319,351.0000,365.5000,350.0000,357.0000,9040

The second column of the file is the date: 2020-03-19 in this case.

I use the COPY FROM command to update the postgres companies table.

COPY companies (ticker, date, open, high, low, close, vol) FROM '/home/user/Downloads/company.csv' using delimiters ',' with null as '\null';

Whenever I use the COPY FROM command to copy the file into postgresql table, my date '20200319' changes to 1970-08-22 20:11:59, and I end up with my last record which looks something like that:

   id    | ticker |        date         | open | high  | low | close | vol  
---------+--------+---------------------+------+-------+-----+-------+------
 2248402 | Atari  | 1970-08-22 20:11:59 |  351 | 365.5 | 350 |   357 | 9040

If I manually update the companies table with the following command, I get proper results:

INSERT INTO companies (ticker, date, open, high, low, close, vol) VALUES ('Atari', to_timestamp('20200319', 'YYYYMMDD')::timestamp without time zone ,351.0000,365.5000,350.0000,357.0000,9040);

However the above solution doesn't work if the data is stored in a csv file.

Proper result:

   id    | ticker |        date         | open | high  | low | close | vol  
---------+--------+---------------------+------+-------+-----+-------+------
 2250513 | Atari  | 2020-03-19 00:00:00 |  351 | 365.5 | 350 |   357 | 9040

My Questions:

Is there a way to change the output date format in COPY FROM command?
What is the proper way to update large postgres tables with daily quotes from csv files in bulk by means of sql commands?

My postgres version: psql (PostgreSQL) 11.7

Edit:

This is Not psql copy question.

Does this answer your question? Importing .csv with timestamp column (dd.mm.yyyy hh.mm.ss) using psql \copy — madflow
That's psql copy. I'm thinking about it as a last resort. My question concerns COPY FROM query. — Andrea
"This is Not psql copy question" \copy is just a wrapper around COPY. They are not fundamentally different things. An answer for one is almost surely an answer for the other as well. In any case, your example works fine for me. Please show a complete example, include the CREATE TABLE statement and any non-default configuration settings. — jjanes
Yes, just tied it and it works. Thanks jjanes for pointing that out. — Andrea
@jjanes to answer you question how the table was created, it was set up with sqlalchemy and later with df.to_sql pandas command. Anyway, its working fine now. Thanks again. — Andrea

Andrea Andrea · Accepted Answer · 2020-03-20T20:03:30

Ok, I took advice from madflow's comments and partially from Abelisto, and adjusted my SET datestyle.

Initially I tied: SET datestyle = 'YYYYMMDD'; (and many more combinations of it)

But was getting the following error: invalid value for parameter "DateStyle": "YYYYMMDD"

I then moved on to trying: set datestyle to "YMD"; And got: SET

Now when I try: show datestyle;

I get:

 DateStyle 
-----------
 ISO, YMD
(1 row)

And, when I try the following command:

   COPY companies (ticker, date, open, high, low, close, vol) FROM '/home/user/Downloads/company.csv' using delimiters ',' with null as '\null';

It looks like I'm finally getting the right date format, so no need to adjust the COPY FROM command:

   id    | ticker |        date         | open | high  | low | close |  vol  
---------+--------+---------------------+------+-------+-----+-------+-------
 1379256 | Atari  | 2020-03-16 00:00:00 |  294 | 337.5 | 256 |   337 | 48690
 1379257 | Atari  | 2020-03-17 00:00:00 |  347 |   381 | 338 |   357 | 36945
 1379258 | Atari  | 2020-03-18 00:00:00 |  364 |   380 | 350 |   357 | 19650
 2251920 | Atari  | 2020-03-19 00:00:00 |  351 | 365.5 | 350 |   357 |  9040

So, thanks guys for suggestions!

Copy file from CSV into Postgresql table - timestamp problem

1 Answers