Thread: COPY - Ignore Problems

I wish to insert data into a table from a very large text file (from a
cron script) using COPY. However if the lName (TEXT), fName(TEXT),
workDate(DATE) already exist I don't want to insert data and just want
to move onto the next record. Is there any way I can tell my bash
script/COPY to ignore the case where the unique constraint exists
(lName,fName,workDate), and move on to the next record?

ps 7.4, debian stable
TIA
Ron

How about this:

Let's call your current table tab.
Insert into a table with the same shape as your table tab called 'lfd'.
Create an index on table lfd on fields lname, fname, workdate.
Delete from lfd where lfd.lname = tab.lname and lfd.fname = tab.fname
and lfd.workdata = tab.workdate
Insert into tab select * from lfd

Or something like that.  SQL*Server has something called
IGNORE_DUPLICATES.  It is the only database I recall that has that
feature.

> -----Original Message-----
> From: Ron St-Pierre [mailto:rstpierre@syscor.com]
> Sent: Monday, December 22, 2003 3:48 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] COPY - Ignore Problems
>
>
> I wish to insert data into a table from a very large text
> file (from a
> cron script) using COPY. However if the lName (TEXT), fName(TEXT),
> workDate(DATE) already exist I don't want to insert data and
> just want
> to move onto the next record. Is there any way I can tell my bash
> script/COPY to ignore the case where the unique constraint exists
> (lName,fName,workDate), and move on to the next record?
>
> ps 7.4, debian stable
> TIA
> Ron
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

On Mon, 2003-12-22 at 23:47, Ron St-Pierre wrote:
> I wish to insert data into a table from a very large text file (from a
> cron script) using COPY. However if the lName (TEXT), fName(TEXT),
> workDate(DATE) already exist I don't want to insert data and just want
> to move onto the next record. Is there any way I can tell my bash
> script/COPY to ignore the case where the unique constraint exists
> (lName,fName,workDate), and move on to the next record?

CREATE TEMPORARY TABLE mytemp (
      LIKE real_table, PRIMARY KEY (lname, fname, workdate)
);
COPY mytemp FROM '/my/input/file';
INSERT INTO real_table (
      SELECT * FROM mytemp AS t
         WHERE NOT EXISTS (
            SELECT * FROM real_table AS r
              WHERE r.lname = t.lname AND
                    r.fname = t.fname AND
                    r.workdate = f.workdate
         )
      );

NB: your mixed case names are automatically converted to lower case
unless you quote them.
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "And this shall be a sign unto you; Ye shall find the
      babe wrapped in swaddling clothes, lying in a manger."
                              Luke 2:12

Thanks Dann, Michael and Oliver. I guess that a temporary table is what
I should use. I thought about doing it that way initially but thought
I'd see if perhaps I was missing a better way.
Ron


Ron St-Pierre wrote:

> I wish to insert data into a table from a very large text file (from a
> cron script) using COPY. However if the lName (TEXT), fName(TEXT),
> workDate(DATE) already exist I don't want to insert data and just want
> to move onto the next record. Is there any way I can tell my bash
> script/COPY to ignore the case where the unique constraint exists
> (lName,fName,workDate), and move on to the next record?
>
> ps 7.4, debian stable
> TIA
> Ron
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>