Question 385424 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – What type of data should I choose for a column in pgAdmin 4 when the data in the CSV file I'm trying to import is "$##,##"?

I’m trying to import a tabel from a CSV file. The table has two columns whose values are "$##,##". When I try to import I get an error. the error

I’ve already tried using Numeric, Double Precision and Money my column data types as the data type to no avail. What am I doing wrong? Thanks 😉

Tags:

2

Answers


  1. 0

    You should use numeric(15,2).

    And yes, you won’t be able to load the data using COPY. One way to deal with that is to use COPY to load the data into a temporary table, where the column has data type text. In a second step, you can use something like

    INSERT INTO realtable (money_col, ...)
SELECT to_number(money_col, '"$"99D99'), ...
FROM temptable;

    Set lc_numeric to a German locale to have PostgreSQL recognize the comma as a decimal point.

    Login or Signup to reply.
  2. 0

    I’m sure there are workarounds within PostgreSQL itself, but when I’m faced with this issue I tend to take the simplest route: I import the CSV file into Excel, reformat the columns to remove commas, and then save back to CSV.

    Not always possible, I know, but it is simple. You just have to be careful when bringing CSVs into Excel that you import the data rather than just clicking on the file to open it. Excel will reformat numbers that should be stored as text (such as U.S. Zip codes that begin with a zero).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search