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:
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:
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 ...
Let me know if you need more help.
United States PostgreSQL Association - http://www.postgresql.us
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily
In response to
pdxpug by date
|Next:||From: Roth, Gabrielle||Date: 2008-04-10 15:02:14|
|Subject: April meeting in one week!|
|Previous:||From: Thomas Keller||Date: 2008-04-03 21:37:34|
|Subject: copy or update question|