I need import a file to my Postgres database and get this error:
invalid input syntax for integer in fabrica, "1";
SQL state: 22P02
my command is:
copy trazabilidade(fabrica, --integer
idChapa, --integer
descricao, --varchar
espessura, --double precision
comprimento, --double precision
largura, --double precision
peso) from 'C:/temp_nexo/traz.csv' delimiter ';';
How can I import data from csv file types that have numbers?
http://wiki.postgresql.org/wiki/COPY
Can not extend Pg coercions
The data-loading mechanism relies on the data being a formal representation of a Pg data-type, or coercible (e.g, cast'able) by Pg. However, there isn't currently a way to add custom-coercions for the Pg types. You can not for instance, make '31,337'::int work by overriding the coercion to an Int.
It also suggests two alternatives, namely pgloader.
pgloader is much better at loading error-prone data in a more flexible format than the built-in COPY is. The downsides are additional install complexity (Python+psycopg+configuration) and a sometimes significant speed loss compared with the built-in COPY.
As per Denis's reply about the COPY command, you can't add custom coercions to postgres copy commands. If pgloader is overkill, you can load your data to a temp table and then from there examine, then cast/trim/manipulate any data you think should be valid.
