[SQL] 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 <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] import ignoring duplicates
Mark Fenbers 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 10 records I would opt for the latter. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] import ignoring duplicates
On Sun, May 16, 2010 at 12:38 PM, Mark Fenbers 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
