1

my command is

\copy meta.amz_payment1("Date", settlement_id, type, order_id, sku, 

description, quantity, marketplace, fulfillment, order_city, order_state, 

order_postal, product_sales, shipping_credits, promotional_rebates, 

sales_tax_collected, selling_fees, fba_fees, other_transaction_fees,other, total) 

from '/Users/manish/Downloads/amz.csv' delimiter ',' csv header

but it gives following error :

ERROR: invalid input syntax for type numeric: "-8,791.41" CONTEXT: COPY amz_payment1, line 23, column total: "-8,791.41"

Improve this question

1 Answer 1

Reset to default
1

The column "total" is of type numeric, but the CSV file has a formatted string for that position. You should add a varchar column to your meta.amz_payment1 table and copy the information into that column. After you copied in the data, you can populate the "total" column with an UPDATE statement:

ALTER TABLE meta.amz_payment1 ADD COLUMN total_fmt varchar;

\copy meta.amz_payment1("Date", ... total_fmt) from ...;

UPDATE meta.amz_payment1 SET total = total_fmt::numeric;

then

ALTER TABLE meta.amz_payment1 DROP COLUMN total_fmt;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

total_ft does not exist while trying to run
First add that column to your table, see updated answer
ERROR: invalid input syntax for type numeric: "-8,791.41" SQL state: 22P02 while trying to run update command after add column
Have you changed the \copy command to use the new column with varchar data type instead of "total"?
total_fmt::numeric will fail with the same error as the original \copy. The problem is that the text->numeric cast doesn't allow commas in the input. Postgres provides to_char() and to_number() to deal with locale-dependent numeric formatting. Though for a one-off import, I'd just strip the commas with replace().