Only numbers less than 1 exported correctly to csv Postgresql-CodePudding

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?

CodePudding user response：

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