1

My PostgreSQL database version is

PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

In both databases, datestyle is set to"ISO, DMY".


ATTEMPT 1

Query :

insert into station(name , date) values('test' , '21/1/2016')

Error Log:

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

********** Error **********

ERROR: date/time field value out of range: "21/1/2016"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.
Character: 8

ATTEMPT 2

Query :

select '21/1/2016'::date

Error log :

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

********** Error **********

ERROR: date/time field value out of range: "21/1/2016"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.
Character: 8

On one system it works and on other system it doesn't. Both have Ubuntu and the same PostgreSQL version.

COLLATE SETTINGS ON BOTH SERVERS

Query works successfuly on this server and has below COLLATE setting

show lc_CTYPE   -> C
show LC_CoLLATE -> C
SHOW lc_time;   -> "en_IN"
select '21/1/2016'::date   -> "2016-01-21"

Query Does not Works on this server and has below COLLATE setting

show lc_CTYPE  -> "en_IN"
show LC_CoLLATE ->  "en_IN"
SHOW lc_time -> "en_IN"
select '21/1/2016'::date  -> above error log
  • 1
    Just use a sane format, like 'YYYY-MM-DD' or 'YYYYMMDD', everywhere? – ypercubeᵀᴹ Jan 21 '16 at 9:31
  • @ypercubeᵀᴹ i want to use day month year DD-MM-YYYY – vijay Jan 21 '16 at 9:45
  • 2
    Actually, your values are formatted with DD/M/YYYY, not with DD-MM-YYYY. – ypercubeᵀᴹ Jan 21 '16 at 9:51
  • @ypercubeᵀᴹ Thanks , you were right also i wrote here new detailed answer just right now – vijay Jan 22 '16 at 6:25