Postgres Copy - Importing an integer with a comma

Question

I am importing 50 CSV data files into postgres. I have an integer field where sometimes the value is a regular number (comma-delimited) and sometimes it is in quotations and uses a comma for the thousands.

For instance, I need to import both 4 and "4,000".

I'm trying:

COPY race_blocks FROM '/census/race-data/al.csv' DELIMITER ',' CSV HEADER;

And get the error:

ERROR: invalid input syntax for integer: "1,133"

How can I do this?

Answer 1

Let's assume you have only one column in your data. First create temporary table with varchar column:

CREATE TEMP TABLE race_blocks_temp (your_integer_field VARCHAR);

Copy your data from file

COPY race_blocks_tmp FROM '/census/race-data/al.csv' DELIMITER ',' CSV HEADER;

Remove ',' from varchar field, convert data to numeric and insert into your table.

INSERT INTO race_blocks regexp_replace(your_integer_field, ',', '') :: numeric AS some_colun FROM race_blocks_tmp;