import csv files on postgres numeric types

Question

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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 11, 2011 at 17:25

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

Comments

qooleot · Accepted Answer · 2011-06-13 12:01:07Z

1

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.

answered Jun 13, 2011 at 12:01

qooleot

4394 silver badges9 bronze badges

Collectives™ on Stack Overflow

import csv files on postgres numeric types

2 Answers 2

Can not extend Pg coercions

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Can not extend Pg coercions

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related