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.

• genderbee: Thanks, I tried it already before, but error persist.
• Laurenz Albe: What is your datestyle setting, and what is the data type of the column that is to receive the value?
• genderbee: Seems configuration is not saving, after logout from psql console and login back, settings are US, MDY again, not GERMAN, DMY.
• Laurenz Albe: Sure. Set the environment variable PGDATESTYLE as indicated in my extended answer to set the default for all clients that use libpq.
• genderbee: 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?