Обсуждение: [GENERAL] Dealing with number formats when server and client are differentlocales

Rob Northcott <Rob.Northcott@compilator.com> writes:
> Our application was written assuming that the SQL server local settings for number formats would be the same as the
clientmachine 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
localformat 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

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 06 September 2017 13:36
To: Rob Northcott <Rob.Northcott@compilator.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Dealing with number formats when server and client are different locales

Rob Northcott <Rob.Northcott@compilator.com> writes:
> Our application was written assuming that the SQL server local settings for number formats would be the same as the
clientmachine 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
localformat 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
arelike. 
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

----------------------


Thanks for the reply Tom.

The problem we've got is that our app (running on PCs) generates commands to send to the SQL server, such as
UPDATE customers SET cus_balance = 10.4 WHERE cus_key = 'A001'
This has been working historically because the clients were always UK-based.  Now some users are wanting to use
Europeansetups with commas as decimals.  This is fine inside the application itself but any SQL command strings
generatedcome out in the client PC's local format settings (SET cus_balance = 10,4) and the server doesn't like that.
Changingall the code to force formatting to suit the server rather than using local settings would be pretty
time-consuminghence the hope that there was another way. 

I'll have a look at your links though - looks like it may be possible to find a workaround.

Rob

Rob Northcott <Rob.Northcott@compilator.com> writes:
> The problem we've got is that our app (running on PCs) generates commands to send to the SQL server, such as
> UPDATE customers SET cus_balance = 10.4 WHERE cus_key = 'A001'
> This has been working historically because the clients were always UK-based.  Now some users are wanting to use
Europeansetups with commas as decimals.  This is fine inside the application itself but any SQL command strings
generatedcome out in the client PC's local format settings (SET cus_balance = 10,4) and the server doesn't like that. 

I was afraid you were going to say that.  There's exactly no chance that
that syntax would work as you're hoping.  I do not actually believe that
it could work on any flavor of SQL, because of the conflict against what
commas mean for other purposes.

You're gonna have to fix your app.  You could maybe make it insert
to_number() calls, but it'd almost certainly be easier to get it to
output numbers in SQL-standard syntax in the first place.

            regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 06 September 2017 15:49
To: Rob Northcott <Rob.Northcott@compilator.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Dealing with number formats when server and client are different locales

Rob Northcott <Rob.Northcott@compilator.com> writes:
> The problem we've got is that our app (running on PCs) generates
> commands to send to the SQL server, such as UPDATE customers SET cus_balance = 10.4 WHERE cus_key = 'A001'
> This has been working historically because the clients were always UK-based.  Now some users are wanting to use
Europeansetups with commas as decimals.  This is fine inside the application itself but any SQL command strings
generatedcome out in the client PC's local format settings (SET cus_balance = 10,4) and the server doesn't like that. 

>>I was afraid you were going to say that.  There's exactly no chance that that syntax >>would work as you're hoping

>>You're gonna have to fix your app.


Yes I think I am - I've been looking at the links you sent before about lc_numeric and it isn't going to help.  At the
timethis app was written nobody was concerned about foreign formats and didn't think about what would happen if the
decimalseparator wasn't a dot.  Now there is a need for it to be used by people in Scandinavia.  Bummer... 

I'll get on with changing it then

Thanks for the help anyway

Rob