Search code examples
postgresqlcsvbulkinsertsqldatatypespostgresql-copy

Issue with importing formatted numbers over 1,000 with COPY

I have a Postgres database and have been trying to import a CSV file into a table with the code below. I keep getting the error

ERROR:  invalid input syntax for type numeric: " 1,183.26 "

I assume the issue is that there is a , in the value but when I go into the CSV and try to edit the value it seems like the , is added automatically by Excel.

COPY invtest5
FROM 'C:\Users\Hank\Downloads\SampleData\SampleDataCSV.csv' 
DELIMITER ',' 
CSV HEADER;

The table definition:

CREATE TABLE invtest5 (
    OrderDate date,
    Region varchar(255),
    Rep varchar(255),
    Item varchar(255),
    Units int,
    Unit_Cost numeric(15,3),
    Total numeric(15,3)
);

I am looking for a way to import the data whether or not the number has a , in it.

Solution

  • ' 1,183.26 ' is not a valid numeric literal. COPY is fast and simple, but not fault-tolerant. Requires valid input.

    Some options to fix:

    1. Format numbers in Excel without "group separator" (that's what the noise , is).

    2. Edit the CSV to remove group separators. (But don't remove other commas!)

    3. If you can afford to ALTER the column type in the target table (i.e. no concurrent load on the DB, you have the necessary privileges, and no depending objects that would block), you could:

    ALTER TABLE invtest5
  ALTER unit_cost TYPE text
, ALTER total     TYPE text;     -- both columns?

COPY ...

ALTER TABLE invtest5
  ALTER unit_cost TYPE numeric(15,3) USING (replace(unit_cost, ',', '')::numeric)
, ALTER total     TYPE numeric(15,3) USING (replace(total    , ',', '')::numeric);

    The expression (replace(unit_cost, ',', '')::numeric) removes all commas before casting to numeric.

    Leading and trailing whitespace is trimmed in the cast automatically.

    If there are already some rows in the table, existing values are cast back and forth, too, which triggers a whole table rewrite and bloats the table. Not efficient for big tables.

    1. If you cannot easily fix your CSV and cannot afford to tinker with the target table (or just don't want to bloat it), use a temporary staging table as COPY target, then INSERT from there:
    CREATE TEMP tmp_invtest5 AS TABLE invtest5 LIMIT 0;  -- copy basic structure

ALTER TABLE tmp_invtest5
  ALTER unit_cost TYPE text
, ALTER total     TYPE text;     -- both columns?

COPY TO tmp_invtest5 ...

INSERT INTO invtest5 
      (orderdate, region, rep, item, units, unit_cost, total)
SELECT orderdate, region, rep, item, units, replace(unit_cost, ',', '')::numeric
                                          , replace(total    , ',', '')::numeric
FROM   tmp_invtest5
-- ORDER BY ??? -- while being at it?

    The temporary table is dropped automatically at the end of the session. If you need it gone before that, DROP TABLE tmp_invtest5;.

    Related: