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?

You can import to a temp table such numbers as strings and then copy to a real table with apropriate conversion. — Ihor Romanchenko
– Ihor Romanchenko, Commented Sep 9, 2013 at 21:46
@IgorRomanchenko This should be an answer, it's exactly what I was going to suggest. Only say text rather than "strings", since this is PostgreSQL :) — IMSoP
– IMSoP, Commented Sep 9, 2013 at 22:15

zero323 · Accepted Answer · 2013-09-09 22:16:52Z

5

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;

answered Sep 9, 2013 at 22:16

zero323

331k108 gold badges981 silver badges958 bronze badges

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

1 Comment

KBurchfiel Over a year ago

Thank you! My table had multiple columns, but I was still able to use regex_replace. This is what the final step looked like (after renaming my table as a temp table and creating a new table with sales_total in real format: insert into sales(id, product_id, manufacturer, category, product, customer_id, sale_date, sale_time, quantity, price, sale_total) select id, product_id, manufacturer, category, product, customer_id, sale_date, sale_time, quantity, price, regexp_replace(sale_total, ',', '') :: numeric from sales_temp; Also, storing sales_total as text (not VARCHAR) also worked.

Collectives™ on Stack Overflow

Postgres Copy - Importing an integer with a comma

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related