Thread: RE: [INTERFACES] Spanish format on date and numbers
----- Original Message ----- From: James Olin Oden <joden@lee.k12.nc.us> To: Oscar Serrano <oserra@fondos.net> Sent: Sunday, November 28, 1999 7:27 AM Subject: Re: [INTERFACES] Spanish format on date and numbers > > > > This is a multi-part message in MIME format. > > > > ------=_NextPart_000_002B_01BF383C.AE4DF100 > > Content-Type: text/plain; > > charset="iso-8859-1" > > Content-Transfer-Encoding: quoted-printable > > > > > > Hi: > > I'm from Spain. I've exported some tables from MS Access to Postgres. = > > You > > know, in Spain, a number like this: 1,234.56 me write it like this: > > 1.234,56 > > i.e., the thousand separator is the "." and the decimal separator is the = > > "," > > > > I'm going to use Perl (DBI) to get the data from the databases. > > Is there anyway so DBI or postgress gives me the data in the spanish = > > format? > > > > I wouldn't like to use somethink like this: > > $result=3D~s/\./,/g; > > to convert all "." to "," and vice-versa. > > > Certainly you can use the substitution operator to replace the .'s with ,'s > and the ,'s with .'s. The main problem is that the first substitution you > make will cause all one character to become the character you are about > to convert next. As in: > > 1,234.52 > > becomes: > > 1.234.52 > > when replacing your comma. So on the next step you have no nice way of telling > which is the real . you wish to replace with ,. One way around this is to > replace one of the symbols with a third symbol not found in a number: > > $result =~ s/\./\@/g; # Periods to @'s > $result =~ s/\,/\./g; # commas to periods > $result =~ s/\@/\,/g; # @'s to commas > > On the other hand if you don't use the susbstitution operator and instead > use tr, the translation operator, you can get away with doing it in > one fell swoop: > > $result =~ tr/.,/,./; > > and that should do it (-: I never used the tr (translation operator), but I see that in this case is better that the =~s But What I was trying is that postgres returns the values in the spanish format, not having to translate ,'s to .'s and viceversa As I told in previos messages, I thing I must compile Postgres with --use-locale and have defined some variables. But I'm not sure how to define that variables, and in case I have them well defined, I don't know if it is needed extra configuration in my Linux, or have installed any spanish library or whatever. Thank you very much. > ...james > > Thank you very much > > > > ______________________________ > > > > Oscar Serrano > > > > > > > > ------=_NextPart_000_002B_01BF383C.AE4DF100 > > Content-Type: text/html; > > charset="iso-8859-1" > > Content-Transfer-Encoding: quoted-printable > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > > <HTML><HEAD> > > <META content=3D"text/html; charset=3Diso-8859-1" = > > http-equiv=3DContent-Type> > > <META content=3D"MSHTML 5.00.2014.210" name=3DGENERATOR> > > <STYLE></STYLE> > > </HEAD> > > <BODY bgColor=3D#ffffff> > > <DIV><FONT face=3DArial size=3D2><BR>Hi:<BR>I'm from Spain. I've = > > exported some=20 > > tables from MS Access to Postgres. You<BR>know, in Spain, a number like = > > this:=20 > > 1,234.56 me write it like this:<BR>1.234,56<BR>i.e., the thousand = > > separator is=20 > > the "." and the decimal separator is the ","</FONT></DIV> > > <DIV> </DIV> > > <DIV><FONT face=3DArial size=3D2>I'm going to use Perl (DBI) to get the = > > data from=20 > > the databases.<BR>Is there anyway so DBI or postgress gives me the data = > > in the=20 > > spanish format?</FONT></DIV> > > <DIV> </DIV> > > <DIV><FONT face=3DArial size=3D2>I wouldn't like to use somethink like=20 > > this:<BR>$result=3D~s/\./,/g;<BR>to convert all "." to "," and=20 > > vice-versa.</FONT></DIV> > > <DIV> </DIV> > > <DIV><FONT face=3DArial size=3D2>Thank you very much</FONT></DIV> > > <DIV> </DIV> > > <DIV><FONT face=3DArial = > > size=3D2>______________________________</FONT></DIV> > > <DIV> </DIV> > > <DIV><FONT face=3DArial size=3D2>Oscar Serrano</FONT></DIV> > > <DIV> </DIV> > > <DIV><FONT face=3DArial size=3D2></FONT> </DIV></BODY></HTML> > > > > ------=_NextPart_000_002B_01BF383C.AE4DF100-- > > > > > > ************ > > >
On Mon, Nov 29, 1999 at 02:12:15PM +0100, Oscar Serrano wrote: > As I told in previos messages, I thing I must compile Postgres > with --use-locale and have defined some variables. But I'm not sure how to > define that variables, and in case I have them well defined, I don't know if > it is needed extra configuration in my Linux, or have installed any spanish > library or whatever. Does that mean a localized postmaster returns the number with ',' instead of '.'? Mine certainly doesn't. In fact I remove LC_NUMERIC for ECPG to make sure numbers have the '.' as decimal seperator. Could anyone tell me what standard says? Is it "14.7" as my postmaster gives it to me even with LC_ALL=de_DE and --use-locale or is it "14,7" as the German locale asks for (and I have a bug in my setup). If the later is correct ECPG has to go back to the drawing board. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL! ************
On Mon, Nov 29, 1999 at 02:12:15PM +0100, Oscar Serrano wrote: > As I told in previos messages, I thing I must compile Postgres > with --use-locale and have defined some variables. But I'm not sure how to > define that variables, and in case I have them well defined, I don't know if > it is needed extra configuration in my Linux, or have installed any spanish > library or whatever. Does that mean a localized postmaster returns the number with ',' instead of '.'? Mine certainly doesn't. In fact I remove LC_NUMERIC for ECPG to make sure numbers have the '.' as decimal seperator. Could anyone tell me what standard says? Is it "14.7" as my postmaster gives it to me even with LC_ALL=de_DE and --use-locale or is it "14,7" as the German locale asks for (and I have a bug in my setup). If the later is correct ECPG has to go back to the drawing board. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL! ************
----- Original Message ----- From: Michael Meskes <meskes@postgreSQL.org> To: <pgsql-interfaces@postgreSQL.org> Sent: Tuesday, November 30, 1999 11:27 AM Subject: Re: [INTERFACES] Spanish format on date and numbers > On Mon, Nov 29, 1999 at 02:12:15PM +0100, Oscar Serrano wrote: > > As I told in previos messages, I thing I must compile Postgres > > with --use-locale and have defined some variables. But I'm not sure how to > > define that variables, and in case I have them well defined, I don't know if > > it is needed extra configuration in my Linux, or have installed any spanish > > library or whatever. > > Does that mean a localized postmaster returns the number with ',' instead of > '.'? Mine certainly doesn't. Well, I'm not sure. That's what I wanted to know. Reading the not very clear documentation at this point, I understand that a spanish localiced postmaster returns the the float numbers with ',' instead of '.' I've seen that declaring don't-remember-what variable, the dates were returned in the west-european format (dd/mm/aa). Won't be a similar variable to the number format? > In fact I remove LC_NUMERIC for ECPG to make sure numbers have the '.' as > decimal seperator. Reading the documentation of the installation process, I didn't see the LC_NUMERIC variable anywhere. I only saw references to the LC_ALL, LC_CTYPE, and LANG. > Could anyone tell me what standard says? Is it "14.7" as my postmaster gives > it to me even with LC_ALL=de_DE and --use-locale or is it "14,7" as the > German locale asks for (and I have a bug in my setup). If the later is > correct ECPG has to go back to the drawing board. The standard, I think, is "14.7" and not "14,7" if you don't use-locale. Can anybody tell us what variables and how must they be defined before compile with --use-locale to make the postmaster return values in the spanish format? Thanks. > Michael > > -- > Michael Meskes | Go SF 49ers! > Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! > Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! > Email: Michael@Fam-Meskes.De | Use PostgreSQL! > > ************ > > > > ************ > ************
----- Original Message ----- From: Michael Meskes <meskes@postgreSQL.org> To: <pgsql-interfaces@postgreSQL.org> Sent: Tuesday, November 30, 1999 11:27 AM Subject: Re: [INTERFACES] Spanish format on date and numbers > On Mon, Nov 29, 1999 at 02:12:15PM +0100, Oscar Serrano wrote: > > As I told in previos messages, I thing I must compile Postgres > > with --use-locale and have defined some variables. But I'm not sure how to > > define that variables, and in case I have them well defined, I don't know if > > it is needed extra configuration in my Linux, or have installed any spanish > > library or whatever. > > Does that mean a localized postmaster returns the number with ',' instead of > '.'? Mine certainly doesn't. Well, I'm not sure. That's what I wanted to know. Reading the not very clear documentation at this point, I understand that a spanish localiced postmaster returns the the float numbers with ',' instead of '.' I've seen that declaring don't-remember-what variable, the dates were returned in the west-european format (dd/mm/aa). Won't be a similar variable to the number format? > In fact I remove LC_NUMERIC for ECPG to make sure numbers have the '.' as > decimal seperator. Reading the documentation of the installation process, I didn't see the LC_NUMERIC variable anywhere. I only saw references to the LC_ALL, LC_CTYPE, and LANG. > Could anyone tell me what standard says? Is it "14.7" as my postmaster gives > it to me even with LC_ALL=de_DE and --use-locale or is it "14,7" as the > German locale asks for (and I have a bug in my setup). If the later is > correct ECPG has to go back to the drawing board. The standard, I think, is "14.7" and not "14,7" if you don't use-locale. Can anybody tell us what variables and how must they be defined before compile with --use-locale to make the postmaster return values in the spanish format? Thanks. > Michael > > -- > Michael Meskes | Go SF 49ers! > Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! > Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! > Email: Michael@Fam-Meskes.De | Use PostgreSQL! > > ************ > > > > ************ > ************
[Oops, apparently my mail did get through. Sorry for resending the question to the list.] On Fri, Dec 03, 1999 at 03:44:01PM +0100, Oscar Serrano wrote: > Well, I'm not sure. That's what I wanted to know. Reading the not very clear > documentation at this point, I understand that a spanish localiced > postmaster returns the the float numbers with ',' instead of '.' So that would mean the output honors locale setting. But the input does not at least not always. After all you cannot type a constant 14,7 in C either. But how about a string? > > In fact I remove LC_NUMERIC for ECPG to make sure numbers have the '.' as > > decimal seperator. > > Reading the documentation of the installation process, I didn't see the > LC_NUMERIC variable anywhere. I only saw references to the LC_ALL, LC_CTYPE, > and LANG. I don't care about the settings other than the numerical one. So I only disabled LC_NUMERIC. But if the postmaster honors locale this solution is not really working. > The standard, I think, is "14.7" and not "14,7" if you don't use-locale. Sure. But what if I do use-locale? > Can anybody tell us what variables and how must they be defined before > compile with --use-locale to make the postmaster return values in the > spanish format? Hopefully the answer is that postmaster never does or else I have no idea how to program libecpg. I would need a way to ask postmaster which locale he runs in. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL! ************
[Oops, apparently my mail did get through. Sorry for resending the question to the list.] On Fri, Dec 03, 1999 at 03:44:01PM +0100, Oscar Serrano wrote: > Well, I'm not sure. That's what I wanted to know. Reading the not very clear > documentation at this point, I understand that a spanish localiced > postmaster returns the the float numbers with ',' instead of '.' So that would mean the output honors locale setting. But the input does not at least not always. After all you cannot type a constant 14,7 in C either. But how about a string? > > In fact I remove LC_NUMERIC for ECPG to make sure numbers have the '.' as > > decimal seperator. > > Reading the documentation of the installation process, I didn't see the > LC_NUMERIC variable anywhere. I only saw references to the LC_ALL, LC_CTYPE, > and LANG. I don't care about the settings other than the numerical one. So I only disabled LC_NUMERIC. But if the postmaster honors locale this solution is not really working. > The standard, I think, is "14.7" and not "14,7" if you don't use-locale. Sure. But what if I do use-locale? > Can anybody tell us what variables and how must they be defined before > compile with --use-locale to make the postmaster return values in the > spanish format? Hopefully the answer is that postmaster never does or else I have no idea how to program libecpg. I would need a way to ask postmaster which locale he runs in. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL! ************
> I've seen that declaring don't-remember-what variable, the dates were
> returned in the west-european format (dd/mm/aa).
> Won't be a similar variable to the number format?
No, unfortunately they are not related. The date/time routines can't
simply use localization, because:
1) the allowed time range for several date/time types exceeds the
Unix-supported range.
2) localization for input supports only one style at a time in
general.
We've got explicit code to allow several (4) different date/time
styles, which is what gives you the western European format.
By contrast, we use the system-supplied numeric i/o routines, so don't
have full control over formatting options. That may be a good thing,
since I would assume the system does a more efficient job than we
would.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
************
> I've seen that declaring don't-remember-what variable, the dates were
> returned in the west-european format (dd/mm/aa).
> Won't be a similar variable to the number format?
No, unfortunately they are not related. The date/time routines can't
simply use localization, because:
1) the allowed time range for several date/time types exceeds the
Unix-supported range.
2) localization for input supports only one style at a time in
general.
We've got explicit code to allow several (4) different date/time
styles, which is what gives you the western European format.
By contrast, we use the system-supplied numeric i/o routines, so don't
have full control over formatting options. That may be a good thing,
since I would assume the system does a more efficient job than we
would.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
************
On Fri, Dec 03, 1999 at 04:38:19PM +0000, Thomas Lockhart wrote: > By contrast, we use the system-supplied numeric i/o routines, so don't > have full control over formatting options. That may be a good thing, > since I would assume the system does a more efficient job than we > would. So postmaster may output a localized number but it cannot parse a statement that contains a localized number, right? If this is correct, I have to honor locales for output but not for input. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL! ************
> > By contrast, we use the system-supplied numeric i/o routines, so don't
> > have full control over formatting options. That may be a good thing,
> > since I would assume the system does a more efficient job than we
> > would.
> So postmaster may output a localized number but it cannot parse a statement
> that contains a localized number, right?
I haven't tested this, but we do use the system libraries for reading
numbers. So if they are locale-enabled, then locale-specific numbers
should be recognized...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Mon, Dec 06, 1999 at 02:02:15AM +0000, Thomas Lockhart wrote: > I haven't tested this, but we do use the system libraries for reading > numbers. So if they are locale-enabled, then locale-specific numbers > should be recognized... Yes, but the parser probably won't. It definitely only lists a decimal point. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL! ************
On Sat, 4 Dec 1999, Michael Meskes wrote:
> On Fri, Dec 03, 1999 at 04:38:19PM +0000, Thomas Lockhart wrote:
> > By contrast, we use the system-supplied numeric i/o routines, so don't
> > have full control over formatting options. That may be a good thing,
> > since I would assume the system does a more efficient job than we
> > would.
>
> So postmaster may output a localized number but it cannot parse a statement
> that contains a localized number, right?
>
> If this is correct, I have to honor locales for output but not for input.
>
I would like write "number" version of the to_char/from_char. It probably
allow convert string with non-PostgreSQL's numbers (with arbitrary
separators etc.) to standard numbers. But I have it in the head now only:-)
(it will available in 7.0).
Karel
----------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
Docs: http://docs.linux.cz (big docs archive)
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
-----------------------------------------------------------------------
On Mon, Dec 06, 1999 at 02:02:15AM +0000, Thomas Lockhart wrote: > I haven't tested this, but we do use the system libraries for reading > numbers. So if they are locale-enabled, then locale-specific numbers > should be recognized... Yes, but the parser probably won't. It definitely only lists a decimal point. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL! ************
On Fri, Dec 03, 1999 at 04:38:19PM +0000, Thomas Lockhart wrote: > By contrast, we use the system-supplied numeric i/o routines, so don't > have full control over formatting options. That may be a good thing, > since I would assume the system does a more efficient job than we > would. So postmaster may output a localized number but it cannot parse a statement that contains a localized number, right? If this is correct, I have to honor locales for output but not for input. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL! ************
Michael Meskes <meskes@postgreSQL.org> writes:
> On Mon, Dec 06, 1999 at 02:02:15AM +0000, Thomas Lockhart wrote:
>> I haven't tested this, but we do use the system libraries for reading
>> numbers. So if they are locale-enabled, then locale-specific numbers
>> should be recognized...
> Yes, but the parser probably won't. It definitely only lists a decimal
> point.
We can't mess around with SQL syntax --- or would you like
SELECT 12,34;
to have locale-dependent results?
However, I think we could make the type-specific I/O routines for
appropriate types be locale-dependent. So you could do
SELECT '12,34'::int4;
and get the desired behavior in a locale where , is the radix point.
OTOH, arrays of int might be tricky...
regards, tom lane
> Yes, but the parser probably won't. It definitely only lists a decimal
> point.
Ah, right! And I'm not sure how to tell it to understand the current
value of the environment variables and to change its behavior.
If we started allowing commas in numeric strings it would conflict
with usual usage; e.g.
select 123,156;
should give you two columns, but might be interpreted instead as a six
digit integer. Note that if you surround any number with single quotes
you will get past the parser unscathed:
select int '123,456';
gets you to the system i/o routines which are locale-enabled.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Mon, Dec 06, 1999 at 09:26:07AM +0100, Karel Zak - Zakkr wrote: > I would like write "number" version of the to_char/from_char. It probably > allow convert string with non-PostgreSQL's numbers (with arbitrary > separators etc.) to standard numbers. But I have it in the head now only:-) > (it will available in 7.0). Sorry, but I don't like that. SQL does ask numbers to use '.' as decimal point and not ','. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
On Mon, Dec 06, 1999 at 10:22:32AM -0500, Tom Lane wrote: > We can't mess around with SQL syntax --- or would you like > > SELECT 12,34; > > to have locale-dependent results? Well that exactly was what I wanted to express. Sorry if I was unclear. > However, I think we could make the type-specific I/O routines for > appropriate types be locale-dependent. So you could do > > SELECT '12,34'::int4; > > and get the desired behavior in a locale where , is the radix point. I'm not sure this is the right thing to do. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
On Mon, Dec 06, 1999 at 03:41:59PM +0000, Thomas Lockhart wrote: > If we started allowing commas in numeric strings it would conflict > with usual usage; e.g. > > select 123,156; And another reason why we should not add locale to SQL. Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
On Tue, 7 Dec 1999, Michael Meskes wrote:
> On Mon, Dec 06, 1999 at 09:26:07AM +0100, Karel Zak - Zakkr wrote:
> > I would like write "number" version of the to_char/from_char. It probably
> > allow convert string with non-PostgreSQL's numbers (with arbitrary
> > separators etc.) to standard numbers. But I have it in the head now only:-)
> > (it will available in 7.0).
>
> Sorry, but I don't like that. SQL does ask numbers to use '.' as decimal
> point and not ','.
>
Uf, what say I about decimal point ',' in (internal) SQL? Nothing...
To_char() is a standard SQL function (see Oracle) which allow you formatting
*output* (example via traditional form for you native language
(Spanish,Czech..)) or (vice versa) allow you read this formated string and
convert it to the *standard* SQL form.
I not want change decimal point in tables, I want feature which allow me
convert it to my lang. What is bad on this?
And.. to_char() is not about decimal point only (it is only a one feature).
Karel
On Tue, Dec 07, 1999 at 12:29:06PM +0100, Karel Zak - Zakkr wrote: > Uf, what say I about decimal point ',' in (internal) SQL? Nothing... Oops, sorry. I see I misunderstood you. > To_char() is a standard SQL function (see Oracle) which allow you formatting > *output* (example via traditional form for you native language > (Spanish,Czech..)) or (vice versa) allow you read this formated string and > convert it to the *standard* SQL form. Sure. > I not want change decimal point in tables, I want feature which allow me > convert it to my lang. What is bad on this? > > And.. to_char() is not about decimal point only (it is only a one feature). Nothing wrong with this. Just my understanding of your mail was wrong. Shouldn't reply to mails while in talking to someone on the phone. :-) Michael -- Michael Meskes | Go SF 49ers! Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire! Tel.: (+49) 2431/72651 | Use Debian GNU/Linux! Email: Michael@Fam-Meskes.De | Use PostgreSQL!
All is right.
> Nothing wrong with this. Just my understanding of your mail was wrong.
> Shouldn't reply to mails while in talking to someone on the phone. :-)
Hmm, multitasking is not problem for software only - I still fight
with this... :-)
Karel