Thread: mass import to table with unique index

Is there a way to mass import (like COPY, INSERT INTO ... SELECT ...) data into an existing table with existing data that has a unique index?

Such as importing data with SSNs, and there's a unique index on the SSN column. MySQL has an 'IGNORE' option for mass imports. Any way with PostgreSQL? Or only with an INSERT command for each record?

On 29 Jan 2003 at 15:00, John Smith wrote:

>
> Is there a way to mass import (like COPY, INSERT INTO ... SELECT ...) data into
> an existing table with existing data that has a unique index?
> Such as importing data with SSNs, and there's a unique index on the SSN column.
> MySQL has an 'IGNORE' option for mass imports. Any way with PostgreSQL? Or only
> with an INSERT command for each record?

I don't understand. Why wouldn't copy work in this case? It does insert only
and it does check index, if I am not making a mistake.

I am not sure you want the contraint in place while it is mass importing. You
can always drop the index, mass import data and recreate index if you are sure
what you are doing..


Bye
 Shridhar

--
Fourth Law of Revision:    It is usually impractical to worry beforehand about
interferences -- if you have none, someone will make one for you.

> -----Original Message-----
> From: Shridhar Daithankar
> [mailto:shridhar_daithankar@persistent.co.in]
> Sent: Wednesday, January 29, 2003 11:13 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] mass import to table with unique index
>
>
> On 29 Jan 2003 at 15:00, John Smith wrote:
>
> >
> > Is there a way to mass import (like COPY, INSERT INTO ...
> SELECT ...)
> > data into
> > an existing table with existing data that has a unique index?
> > Such as importing data with SSNs, and there's a unique
> index on the SSN column.
> > MySQL has an 'IGNORE' option for mass imports. Any way with
> PostgreSQL? Or only
> > with an INSERT command for each record?
>
> I don't understand. Why wouldn't copy work in this case? It
> does insert only
> and it does check index, if I am not making a mistake.
>
> I am not sure you want the contraint in place while it is
> mass importing. You
> can always drop the index, mass import data and recreate
> index if you are sure
> what you are doing..

I think that what the OP is looking for is the SQL*Server equivalent of
option IGNORE_DUP_KEY, where if you try to insert a record with that key
already included, it simply ignores that record.  Hence if you have a
batch of 100 identical records, a single record gets inserted.

It's useful for things like creating dictionaries from a large list of
words.

>>
>> Is there a way to mass import (like COPY, INSERT INTO ... SELECT ...) data into
>> an existing table with existing data that has a unique index?
>> Such as importing data with SSNs, and there's a unique index on the SSN column.
>> MySQL has an 'IGNORE' option for mass imports. Any way with PostgreSQL? Or only
>> with an INSERT command for each record?

>I don't understand. Why wouldn't copy work in this case? It does insert only
>and it does check index, if I am not making a mistake.

>I am not sure you want the contraint in place while it is mass importing. You
>can always drop the index, mass import data and recreate index if you are sure
>what you are doing..

COPY aborts after any encountered errors (like the first duplicate record). Creating a unique index (after dropping it) doesn't work either - it bails with a 'column data isn't unique' error.

> I think that what the OP is looking for is the SQL*Server equivalent of
> option IGNORE_DUP_KEY, where if you try to insert a record with that key
> already included, it simply ignores that record.

Exactly :)

John

On Thu, 30 Jan 2003 01:41:51 -0800 (PST), John Smith
<john_smith_45678@yahoo.com> wrote:
>> I think that what the OP is looking for is the SQL*Server equivalent of
>> option IGNORE_DUP_KEY, where if you try to insert a record with that key
>> already included, it simply ignores that record.
>
>Exactly :)

John,  import into a temporary table and then

INSERT INTO original_table
SELECT DISTINCT ON (your, uniq, cols) *
  FROM temp_table t
 WHERE NOT EXISTS (
    SELECT * FROM original_table o
     WHERE o.your=t.your AND o.uniq=t.uniq AND o.cols=t.cols)
 ORDER BY your, uniq, cols, whatever;

HTH.
Servus
 Manfred