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..

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

---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

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