Re: Dealing with number formats when server and client are different locales

Rob Northcott <Rob(dot)Northcott(at)compilator(dot)com> writes:
> Our application was written assuming that the SQL server local settings for number formats would be the same as the client machine running the application.
> Now there is a need for some clients to run using UK format (full stop for decimal separator) and some to be European format (comma for decimal separator).
> This is causing problems with queries sent to the server because each client app is sending query strings in its own local format and the server throws an error if it doesn't match the server settings.

Hm, Postgres doesn't support comma-for-decimal-point in very many
contexts, so I'm wondering exactly what your queries are like.
If that usage only appears in strings that are processed with
to_number() and a D format character, then maybe you can make this
work, but that seems pretty restrictive.

> Possible solutions I can think of are:

> 1. Change the client application so it checks and server locale settings and formats numbers appropriately.
> 2. Change the server settings to match the client (if this can be set PER SESSION?)

Sure. See lc_numeric.

https://www.postgresql.org/docs/current/static/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION

https://www.postgresql.org/docs/current/static/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT

regards, tom lane