I am addressing an issue where I cannot copy data to postgresql in Rails because the date format is not in matching the default postgresql ISO MDY. On my local machine I just changed the datestyle to European so that my DMY data matches correctly.
I know this has been asked many times but no matter what I try in PostgreSQL 9.4, the datestyle will revert to ISO,MDY within a minute or so. I require the date format: SET datestyle to SQL,DMY; S. Note: the datestyle setting (and other runtime settings) from postgresql.conf can be overridden by an ALTER DATABASE bla SET datestyle., setting it permanently for a single database, or by SET datestyle TO. Setting it for the current session. This latter might be useful when importing some third party data with a different date format.
As usual, its not as easy to do this on the Heroku production server. Using the steps below I try to change the datestyle, which works, but it doesn't persist (as shown by exiting, going back in and asking postgres to show me the datestyle). Any ideas how I can persist this. All research has show not to do this and to change my code to convert the data to MDY but I am pulling millions of rows from a legacy system that defaults as DMY and using a postgresql copy command to do this the fastest way possible.
Seems easier to just tell Postgresql to change datestyle than fix everything else:/ $ heroku pg:psql app-name::CRIMSON= SET datestyle TO 'european'; SET app-name::CRIMSON= SHOW datestyle; DateStyle - ISO, DMY (1 row) app-name::CRIMSON- q $ heroku pg:psql - Connecting to HEROKUPOSTGRESQLCRIMSONURL (DATABASEURL) psql (9.4.1, server 9.3.6) SSL connection (protocol: TLSv1.2, cipher: jibberish, bits: 256, compression: off) Type 'help' for help. App-name::CRIMSON= SHOW datestyle; DateStyle - ISO, MDY (1 row). Any ideas how I can persist this.
Datestyle is a server setting (in postgresql.conf) that client sessions can override (with set datestyle.) for a single transaction or for a single session. There are two ways to persist a change beyond a single session.
![Iso Iso](http://i.stack.imgur.com/75zbS.jpg)
Change the setting in postgresql.conf and restart the server. (Won't work for you on Heroku. You surely don't have privileges to edit postgresql.conf, right?). ALTER DATABASE yourdatabasename SET datestyle TO 'ISO, european'; (Should work for you on Heroku.
I'd set this up as a stand-alone migration.) FWIW, the default isn't ISO MDY, it's ISO, MDY. (Two values, separated by a comma.) The first is the 'output format specification'; the second is the 'input/output specification for year/month/day ordering'. Thank you I'll give that a try and also thanks for clarifying the ISO, DMY setting. Quick clarification, seeing as the database name is different for dev, test and production. Should I wrap the migration in an `if RAILSENV=production' so that it doesn't impact my local environment. Also can I use a config variable say DATABASEURL as the database name in that command?
As per suggestion on this answer. I'll give it a try and report back.
Thanks for the suggestions. And also not sure on the ability to modify the.conf – Mar 17 '15 at 3:21.