1

Software:

Linux cjz-eshop1-p 5.4.0-33-generic #37-Ubuntu SMP Thu May 21 12:53:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg18.04+1)

I am getting errors due to CSV import into the database on my server.

ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244385 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160
ERROR: current transaction is aborted, commands ignored until end of transaction block
Price 244386 ERROR: date/time field value out of range: "30.06.2020"
  Hint: Perhaps you need a different "datestyle" setting.
  Position: 160

But on my local system, the import is working.

I tried almost everything regarding this issue, but no luck (I drew mostly from here).

Any ideas to solve this issue?

UPDATE

Import is done by java. I believe it is not important, because on my local computer, it is working with same OS and same configuration. And as you can see, when I try select related to date (as @Laurenz helped me), it is ok.

postgres@cjz-eshop1-p:~$ psql
psql (12.2 (Ubuntu 12.2-4))
Type "help" for help.

postgres=# SELECT '30.06.2020'::date;
    date    
------------
 30.06.2020
(1 row)

postgres=# 

Before, it was not possible. Do you want some more details?

UPDATE 2

                    if (body[5].length() != 0) {
                        akc_from = "'" + body[5] + "'";
                    } else {
                        akc_from = "null";
                    }

                    if (body[6].length() != 0) {
                        akc_to = "'" + body[6] + "'";
                    } else {
                        akc_to = "null";
                    }

                    if (body[7].length() != 0) {
                        akc_type = body[7];
                    } else {
                        akc_type = "null";
                    }

                    insertPriceCommand = "insert into stg_price(art_no,store_no,sell_pr,akc_price,akc_from,akc_to,akc_type,run_id,proc_flag,proc_note) values(" + art_no + "," + store_no + "," + sell_pr + "," + akc_price + "," + akc_from + "," + akc_to + "," + akc_type + "," + run_id + ",0,null)";
//                System.out.println(insertPriceCommand);
```
3

Follow the hint and adjust your datestyle setting appropriately.

The parameter consists of two parts: the output style and the expected ordering of day, month and year.

With US settings, parsing 30.06.2020 causes an error:

SET datestyle = US, MDY;

SELECT '30.06.2020'::date;

ERROR:  date/time field value out of range: "30.06.2020"
LINE 1: SELECT '30.06.2020'::date;
               ^
HINT:  Perhaps you need a different "datestyle" setting.

This is because there are only 12 months in a year.

You should probably use this setting:

SET datestyle = GERMAN, DMY;

SELECT '30.06.2020'::date;

    date    
------------
 30.06.2020
(1 row)

You can use the options connection parameter to set datestyle when you connect.

19
  • Thanks, I tried it already before, but error persist.
    – genderbee
    Jun 5 '20 at 10:41
  • What is your datestyle setting, and what is the data type of the column that is to receive the value? Jun 5 '20 at 10:46
  • Seems configuration is not saving, after logout from psql console and login back, settings are US, MDY again, not GERMAN, DMY.
    – genderbee
    Jun 5 '20 at 11:20
  • Sure. Set the environment variable PGDATESTYLE as indicated in my extended answer to set the default for all clients that use libpq. Jun 5 '20 at 11:24
  • And how can I set this environment var? In /etc/postgres/12/main/environment? I tried, but no changes. And is there way to set permanent datestyle with SET?
    – genderbee
    Jun 5 '20 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.