PostgreSQL: date/time field value out of range

Question

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);
```

Answer 1

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.