Re: Insert or Replace or \copy (bulkload)

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.