postgresql - How do I alter the date format in Postgres?

Question

I'm getting the following error message

ERROR: date/time field value out of range: "13/01/2010" HINT: Perhaps you need a different "datestyle" setting.

I want to get my date in the format DD/MM/YYYY

Answer 1

SHOW datestyle;

 DateStyle 
-----------
 ISO, MDY
(1 row)

INSERT INTO container VALUES ('13/01/2010');
ERROR:  date/time field value out of range: "13/01/2010"
HINT:  Perhaps you need a different "datestyle" setting.

SET datestyle = "ISO, DMY";
SET

INSERT INTO container VALUES ('13/01/2010');
INSERT 0 1

SET datestyle = default;
SET

http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE

DateStyle - Sets the display format for date and time values, as well as the rules for interpreting ambiguous date input values. For historical reasons, this variable contains two independent components: the output format specification (ISO, Postgres, SQL, or German) and the input/output specification for year/month/day ordering (DMY, MDY, or YMD).

Of course it's best to use unambiguous input format (ISO 8601), but there is no problem to adjust it as you need.