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"

Vivek S.
  • 19,945
  • 7
  • 68
  • 85

1 Answers1

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;
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • total_ft does not exist while trying to run –  May 13 '15 at 08:32
  • First add that column to your table, see updated answer – Patrick May 13 '15 at 08:34
  • ERROR: invalid input syntax for type numeric: "-8,791.41" SQL state: 22P02 while trying to run update command after add column –  May 13 '15 at 08:39
  • Have you changed the `\copy` command to use the new column with `varchar` data type instead of "total"? – Patrick May 13 '15 at 09:49
  • `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()`](http://www.postgresql.org/docs/9.4/static/functions-formatting.html) to deal with locale-dependent numeric formatting. Though for a one-off import, I'd just strip the commas with `replace()`. – Nick Barnes May 13 '15 at 13:55