Thread: allowing ',' (comma) in float8
hi. is it possible to use flort values with a comma as decimal placeholder? i am trying to use copy to insert a table, the table contains a few columns with numerical values such as price, the vales are given on the format "2345,12", postgresql seems to allow only "2345.12". can i change the behavior on postgresql to allow ',' as decimal placeholder? postgresql 7.4.6 /stig
am 12.06.2005, um 12:17:12 +0200 mailte stig erikson folgendes: > hi. > is it possible to use flort values with a comma as decimal placeholder? > > i am trying to use copy to insert a table, the table contains a few columns > with numerical values such as price, the vales are given on the format > "2345,12", postgresql seems to allow only "2345.12". You can pipe the input-file thru tools like sed ore awk and substitute ',' with '.'. This is simple. Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
am 19.06.2005, um 9:23:54 +0200 mailte Andreas Kretschmer folgendes:
> am 12.06.2005, um 12:17:12 +0200 mailte stig erikson folgendes:
> > hi.
> > is it possible to use flort values with a comma as decimal placeholder?
> >
> > i am trying to use copy to insert a table, the table contains a few columns
> > with numerical values such as price, the vales are given on the format
> > "2345,12", postgresql seems to allow only "2345.12".
>
> You can pipe the input-file thru tools like sed ore awk and substitute
> ',' with '.'. This is simple.
A little example:
imagine, there is a input-file like this:
(cols separated with tabs)
,----
| kretschmer@kaufbach:~$ cat insert.sql
| bla 1,4
| blub 2,5
| bla,blub 5,2
`----
with 2 cols, the second are numerical with ','.
,----
| kretschmer@kaufbach:~$ awk 'BEGIN{FS="\t";OFS="\t"}{gsub(",",".",$2);print $0}' < insert.sql
| bla 1.4
| blub 2.5
| bla,blub 5.2
`----
now you have, what you need. The string in the first col contains a ',',
this is not converted.
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
On Sun, Jun 12, 2005 at 12:17:12 +0200, stig erikson <stigerikson_nospam_@yahoo.se> wrote: > hi. > is it possible to use flort values with a comma as decimal placeholder? > > i am trying to use copy to insert a table, the table contains a few columns > with numerical values such as price, the vales are given on the format > "2345,12", postgresql seems to allow only "2345.12". > > can i change the behavior on postgresql to allow ',' as decimal placeholder? > > postgresql 7.4.6 It looks like you can probably use to_number to convert the input string to numeric if ',' is the decimal separator in the locale you are using. You can then cast the numeric value to float.