Hello everybody, following up on this thread: https://www.postgresql.org/message-id/flat/d297e048-ac49-9bed-32e3-9dd4e65d0978%40mail.de specifically on this mail: https://www.postgresql.org/message-id/baef819f-acf0-a64d-c1eb-d2c5da1e5030%40mail.de I hope this idea fulfills the requirements. So let's see what you have to say. to_date is not an immutable function but the best way to parse dates besides casting. Unfortunately, creating an functional index with to_date or casting will fail with an error. Researching the topic in the past and currently, the situation hasn't improved. Many SO solutions or blog posts suggest to roll your own UDF to wrap up an mutable expression and marking it immutable by doing so. This approach has several disadvantages: 1) unawareness of the locale and timezone issues date/timestamp parsing involves 2) hiding errors in production when environment variables are missing/set differently than on dev machines 3) many re-implementations of the same problem I consider 1) the biggest issue. Developers need to be aware of these kinds of issues to solve them properly. This is especially problematic when defining functional indexes where you don't have access to the actual date values. For this purpose, I would like to adhere to the PostgreSQL roadmap and "scratch my own itch" by implementing the following solution which I think best solves the issue at hand: ** Idea ** An additional, immutable variant of to_date with a third parameter to specify the locale by which the date should be parsed. The additional documentation line (https://www.postgresql.org/docs/9.6/static/functions-formatting.html) would look like this: to_date(text, text, text) date convert string to date given a specific locale to_date('05 Dez 2000', 'DD Mon YYYY', 'de_DE') I think this approach has the following advantages: 1) a single, recommended and safe way to parse dates 2) make people aware of the locale/timezone issue but give them a standard tool to solve it 3) eventually make all those and related Google entries (https://www.google.de/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=postgresql+to_date+immutable&*) point to the same and safe solution ** Rejected Ideas ** Geoff suggest to simply mark to_date as stable and being able to parse dates by trying all locales. Two arguments work against this ideas: 1) it would create prohibitively large lookup tables and 2) some mappings are ambiguous: https://www.postgresql.org/message-id/aba44f78-2b84-e752-9b6f-3784bd0f981c%40mail.de Another suggestion from Geoff >>format string with an additional locale "{locale=en_US}"<< couldn't fly as well because a function cannot be set immutable for some inputs. What do you think? Regards, Sven
