Copy file from CSV into Postgresql table - timestamp problem

user_5934625 0
Viewed 1874

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:

  1. Is there a way to change the output date format in COPY FROM command?

  2. 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.

csv copy postgresql
1 Answers

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!

user_59346250
Related