Error at copy command in postgresql?

Question

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"

Patrick · Accepted Answer · 2015-05-13 08:34:09Z

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;

edited May 13, 2015 at 8:34

answered May 13, 2015 at 8:29

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

5 Comments

user3946530 Over a year ago

total_ft does not exist while trying to run

Patrick Over a year ago

First add that column to your table, see updated answer

user3946530 Over a year ago

ERROR: invalid input syntax for type numeric: "-8,791.41" SQL state: 22P02 while trying to run update command after add column

Patrick Over a year ago

Have you changed the \copy command to use the new column with varchar data type instead of "total"?

Nick Barnes Over a year ago

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().

Collectives™ on Stack Overflow

Error at copy command in postgresql?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related