Re: Insert or Replace or \copy (bulkload)

On Tue, 2007-08-14 at 10:16 -0500, Scott Marlowe wrote:
> On 8/14/07, Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> wrote:
> > I'm seeing an obstacle in my aim to migrate from mysql to PG mainly from
> > the manner in which PG handles duplicate entries either from primary
> > keys or unique entries.
> >
> > Data is taken from perl DBI into (right now) CSV based files to be used
> > via psql's \copy command to insert into the table.
> >
> > In MySql, I was using mysqlimport --replace which essentially provided
> > the means to load data into the DB, while at the same time, would
> > provide the necessary logic to replace the entire row if there was a
> > duplicate instead of dying.
> >
> > Under PG, I've yet to found something similar to this functionality and
> > searches via google has uncovered that this is one thing which _still_
> > has not found its way into PG. (anyone knows why? Standards?)
> >
> > Anyway, I found a workaround, but, to me, even though it provides a
> > means to an end, it still looks like it'll end up as a maintenance
> > nightmare each time a table has any additional columns added.
> >
> > Solution is taken from this site:
> >
> > http://www.pointwriter.com/blog/index.php?/archives/6-REPLACE-in-PostgreSQL.html
>
> Example code snipped for brevity
>
> > Can anyone tell me if this won't turn out to be a maintenance nightmare?
> > So, the pertinent question is, is there a better mousetrap available?
>
> I don't see why it would be a maintenance nightmare. Looks pretty
> much like you just create it and go. Once it's in place it should
> just work. There are other ways to skin this particular cat, but that
> one seems as good as any.

I just ran into trouble with this. This rule seems to work when I do
simple inserts, but as what I will be doing will be doing \copy
bulkloads, it will balk and fail.

Is there a suitable/replacement rule which can be used for this purpose
which will work bulkload wise?

Now would be a good idea to teach me how to skin the cat differently.