How to set Postgresql database to see date as “MDY” permanently

Question

Ho can I set my database to see the 'date' as "MDY" without running:

SET datestyle = "ISO, MDY";

every-time I'm trying to access it? I'm using Postgresql version 9.1, Ubuntu 12.04. My system locale, at the time of the database installation was set to en_CA.utf8 and I recently changed it to en_US.utf8.

show lc_CTYPE

return --> "en_CA.UTF-8"

show LC_CoLLATE

return --> "en_CA.UTF-8"

Thank you!

Answer 1

I used to have a very similar problem years ago, and then discovered that I can do

ALTER DATABASE database_name SET datestyle TO "ISO, MDY";

Try that. (This works on a per database basis, for a solution for all of your databases see @a_horse_with_no_name's commment below.)

EDIT: Unfortunately, I cannot test other locales, but @swasheck's comment made me note that with my settings:

test=# SHOW lc_ctype;
      lc_ctype
--------------------
 Hungarian, Hungary
(1 row)


test=# SHOW lc_time;
      lc_time
--------------------
 Hungarian, Hungary
(1 row)

setting datestyle has the following effect:

SET datestyle TO "ISO, MDY";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)

SET datestyle TO "ISO, YMD";

SELECT current_date;
    date
------------
 2012-06-21
(1 row)

SET datestyle TO "postgres, MDY";

SELECT current_date;
    date
------------
 06-21-2012
(1 row)

Apparently, the locale has an effect on these settings.