PostgreSQL how to read csv file with decimal comma?

Question

I try to read a csv file containing real numbers with a comma as separator. I try to read this file with \copy in psql:

\copy table FROM 'filename.csv' DELIMITER ';' CSV HEADER;

psql does not recognize the comma as decimal point.

psql:filename.sql:44: ERROR: invalid input syntax for type real: "9669,84" CONTEXT: COPY filename, line 2, column col-3: "9669,84"

I did some googling but could not find any answer other than "change the decimal comma into a decimal point". I tried SET DECIMALSEPARATORCOMMA=ON; but that did not work. I also experimented with some encoding but I couldn't find whether encoding governs the decimal point (I got the impression it didn't).

Is there really no solution other than changing the input data?

user7070613 · Accepted Answer · 2021-10-02 15:20:55Z

2

COPY to a table where you insert the number into a varchar field. Then do something like in psql:

--Temporarily change numeric formatting to one that uses ',' as
--decimal separator.
set lc_numeric = "de_DE.UTF-8";
--Below is just an example. In your case the select would be part of 
--insert into the target table. Also the first part of to_number
--would be the field from your staging table.
select to_number('9669,84', '99999D999');
9669.84

You might need to change the format string to match all the numbers. For more information on what is available see Data formatting Table 9.28. Template Patterns for Numeric Formatting.

answered Oct 2, 2021 at 15:20

user7070613

Sign up to request clarification or add additional context in comments.

Comments

Akaisteph7 · Accepted Answer · 2024-02-28 22:58:37Z

0

The issue is that the usage of quotes indicate this is a string.

So, either:

the data type of the db column needs to be updated to match
the input data needs to not indicate it is a string (e.g., the SQuirrel client automatically formats the floats and that causes this issue - that can be turned off when exporting data as shown here)

edited Feb 28, 2024 at 22:58

answered Feb 28, 2024 at 22:05

Akaisteph7

6,9212 gold badges39 silver badges53 bronze badges

2 Comments

amphetamachine Over a year ago

AFAIK CSV does not indicate data types associated with its columns.

Akaisteph7 Over a year ago

@amphetamachine The db does. That's what I'm talking about.

Collectives™ on Stack Overflow

PostgreSQL how to read csv file with decimal comma?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related