Re: copy or update question

Hi Tom!

On Thu, Apr 3, 2008 at 2:37 PM, Thomas Keller <kellert(at)ohsu(dot)edu> wrote:

> I have a large (6K records) csv file for which I would like to use COPY if
> the record is new, or UPDATE if the primary key already exists in the table.
> Can I do that with psql?

This thread might be helpful to you:

http://bytes.com/forum/thread422510.html

If you know that the data you're working with isn't being updated by
anyone else at the same time, UPDATE followed by an INSERT that tests
for the updated data would work ok.

Here's what you'd do for each line:

UPDATE foo set thing='stuff' where name = 'xx' and thing<>'stuff';
INSERT into foo (name, thing) (select 'xx' as name, 'stuff' as thing where
not exists (select 1 from foo where name='xx'));

A simple perl wrapper would do the trick - either to make a file that
you pipe to psql, or to perform the transactions directly.

There is a race condition, however:
http://groups.google.com/group/comp.databases.postgresql.general/msg/b49cd7b8c5858746

Otherwise, you can create a stored procedure in SQL or pl/pgsql:

create function insertorupdate(....)
UPDATE mytable WHERE ... SET ...
IF NOT FOUND THEN
INSERT INTO mytable ...
END IF;

Let me know if you need more help.

-selena

--
Selena Deckelmann
United States PostgreSQL Association - http://www.postgresql.us
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily