I downloaded a csv file for practising where date format is of two types as shown in picture. the picture is here I tried to change the format to yyyy-mm-dd in excel but it is not happening. and also, I can’t upload the file in database in my postgresql. I used the data type “date” but it says I need a different datestyle.
code I have used:
create table sample( region varchar, country varchar, item_type varchar, sales_channel varchar, order_priority varchar, order_date date, order_id bigint, ship_date date, unit_sold int, unit_price decimal, unit_cost decimal, total_revenue decimal, total_cost decimal, total_profit decimal); copy sample from 'E:postgresqlbin5m Sales Records.csv' delimiter ',' csv header; ERROR: date/time field value out of range: "3/26/2016" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY sample, line 2, column ship_date: "3/26/2016" SQL state: 22008
any guidance will be helpful, thanks
Advertisement
Answer
To summarize comments into sort of an answer:
create table csv_test(id integer, date_fld date); --CSV file(csv_test.csv 1, 2021-07-11 2, 7/11/2021 3, 7/14/2021 show datestyle ; DateStyle ----------- ISO, MDY copy csv_test from '/home/aklaver/csv_test.csv' with csv; COPY 3 select * from csv_test ; id | date_fld ----+------------ 1 | 2021-07-11 2 | 2021-07-11 3 | 2021-07-14 (3 rows) set datestyle = 'iso, dmy'; SET copy csv_test from '/home/aklaver/csv_test.csv' with csv; ERROR: date/time field value out of range: " '7/14/2021'" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: COPY csv_test, line 3, column date_fld: " '7/14/2021'"
CSV values are text, so your date needs only to be in a correctly formatted date style. The second copy failed because the date style date order was ‘dmy’ and the value is 7/14/2021
and 14 is not a month number. This is why there is a date/month order setting, as 7/11/2021
could either be ‘July 11 2021’ or ‘November 7 2021’. Postgres needs the user to tell it what ordering it is looking at.