Обсуждение: import ignoring duplicates
I am using psql's \copy command to add records to a database from a file. The file has over 100,000 lines. Occasionally, there is a duplicate, and the import ceases and an internal rollback is performed. In other words, no data is imported even if the first error occurs near the end of the file. I am looking for an option/switch to tell psql (or the \copy command) to skip over any duplicate key constraint viloations and continue to load any data that doesn't violate a duplicate key constraint. Is there such an option? Mark
Вложения
Mark Fenbers <Mark.Fenbers@noaa.gov> wrote: > I am using psql's \copy command to add records to a database > from a file. The file has over 100,000 lines. > Occasionally, there is a duplicate, and the import ceases > and an internal rollback is performed. In other words, no > data is imported even if the first error occurs near the end > of the file. > I am looking for an option/switch to tell psql (or the \copy > command) to skip over any duplicate key constraint > viloations and continue to load any data that doesn't > violate a duplicate key constraint. Is there such an > option? No. You can either disable the constraint temporarily, im- port the data, fix any duplicates and re-enable the con- straint, or you can load the data in a temporary table and then transfer only the valid data. With only 100000 records I would opt for the latter. Tim
On Sun, May 16, 2010 at 12:38 PM, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote: > I am using psql's \copy command to add records to a database from a file. > The file has over 100,000 lines. Occasionally, there is a duplicate, and > the import ceases and an internal rollback is performed. In other words, no > data is imported even if the first error occurs near the end of the file. > > I am looking for an option/switch to tell psql (or the \copy command) to > skip over any duplicate key constraint viloations and continue to load any > data that doesn't violate a duplicate key constraint. Is there such an > option? Sounds like you want this: http://pgfoundry.org/projects/pgloader/ Note that copy is optimized to work in a single transaction. Breaking those semantics WILL result in a slow load time, and there's not much you can do about that.