Re: Best strategy for bulk inserts where some violate unique constraint?

On 11/06/2013 07:02 AM, Daniele Varrazzo wrote:
> On Wed, Nov 6, 2013 at 2:54 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>> On 11/06/2013 06:50 AM, Denis Papathanasiou wrote:
>>>
>>> On Tue, Nov 5, 2013 at 6:31 PM, Daniele Varrazzo
>>>
>>> I saw Adrian's reply about how this still won't do what I need in terms
>>> of ignoring the constraint violators and keeping the valid entries; will
>>> switching autocommit to True first have the desired effect?
>>
>> No, a particular COPY is treated as a single transaction.
>
> If I'm not mistaken, copying into a temporary table and then inserting
> only the wanted record into the target table should work as expected
> and leave no bloat. Staying into the psycopg realm it should be the
> most efficient way to load data; outside psycopg world there could be
> more efficient stand-alone solutions.

Agreed and that is what I often do, it just adds a step.

>
> -- Daniele
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com