I want to export this results:
id weight ref_id multiplier cat
1 1,2 1B 1,4 380
2 0,8 1C 1 379
Where id is int8, weight and multiplier are numeric, ref_id is varchar(50) and cat is int4.
The problem is that when I export to csv the weight and multiplier values which are greater or equal to 1 becomes numbers in the order of thousands, while the rest (in this case 0,8) remains the same (as I want it to be).
How can I solve this so the numbers remain the same after export?
>Solution :
You are dealing with locale issues. The CSV data is using , as decimal separator and the database is using a locale that sees , as thousands separator. To confirm in psql do: show lc_numeric;. Add answer as update to question. Two possible solutions 1) Change the , to . in the CSV data. 2) Import the data into staging table where the fields are all varchar. Then use to_number from here Data formatting to convert the numbers on transfer to final table. This will involve temporarily changing the locale numeric setting:
show lc_numeric;
lc_numeric
-------------
en_US.UTF-8
select to_number('1,2', '9D9');
ERROR: numeric field overflow
DETAIL: A field with precision 1, scale 0 must round to an absolute value less than 10^1.
select to_number('1,2', '9G9');
to_number
-----------
12
set lc_numeric = 'de_DE.UTF-8';
SET
production_(postgres)(5442)=# show lc_numeric;
lc_numeric
-------------
de_DE.UTF-8
select to_number('1,2', '9D9');
to_number
-----------
1.2