5

I was wondering if it possible? If a row for some reason cannot be imported ex. duplicate primary key, wrong input type etc etc can it be ignored and move to the next row?

I'm getting this

ERROR:  duplicate key value violates unique constraint "team_pkey"
DETAIL:  Key (team)=(DEN) already exists.
CONTEXT:  COPY team, line 23: "DEN,Denver,Rockets,A"

There's a lot of mistakes in the file and its a pretty big one, so is it possible to ignore the rows that can't be inserted?

Cœur
  • 37,241
  • 25
  • 195
  • 267
LefterisL
  • 1,132
  • 3
  • 17
  • 37
  • a possible approach can be found here http://stackoverflow.com/questions/13947327/to-ignore-duplicate-keys-during-copy-from-in-postgresql - would that be usable in your case? – fvu Jan 16 '14 at 01:28
  • that would solve the duplicate issue but how can i solve the invalid type one? – LefterisL Jan 16 '14 at 01:30
  • possible duplicate of [postgresql: \copy method enter valid entries and discard exceptions](http://stackoverflow.com/questions/20169372/postgresql-copy-method-enter-valid-entries-and-discard-exceptions) – Craig Ringer Jan 16 '14 at 05:21
  • This is oft-discussed, see the linked answers on the "possible duplicate" link I posted above. – Craig Ringer Jan 16 '14 at 05:22
  • Yes i managed to solve the duplicate issue, but is there any way to skip the invalid lines? – LefterisL Jan 16 '14 at 09:45
  • possible duplicate of [Ignore duplicates when importing from CSV](http://stackoverflow.com/questions/21482695/ignore-duplicates-when-importing-from-csv) – jww May 01 '14 at 10:38

1 Answers1

3

A solution that handles the duplicate key issue is described in To ignore duplicate keys during 'copy from' in postgresql - in short using an unconstrained temp table and select distinct on uniquefield into the destination table.

Another way would involve using pgLoader. Unfortunately the documentation seems to have disappeared from the website, but there are several tutorial article on the author's site. It has rich functionality to help you read data with issues, and can do things like store rejected lines in a separate file, transform fields and so on.

Something that may not be obvious immediately: pgLoader version 2 is written in Python, version 3 is written in Lisp. Both can be obtained from the GitHub page.

Community
  • 1
  • 1
fvu
  • 32,488
  • 6
  • 61
  • 79