Skip site navigation (1) Skip section navigation (2)

Re: copy or update question

From: "Selena Deckelmann" <selenamarie(at)gmail(dot)com>
To: "Thomas Keller" <kellert(at)ohsu(dot)edu>
Cc: "Postgresql PDX_Users" <pdxpug(at)postgresql(dot)org>
Subject: Re: copy or update question
Date: 2008-04-03 22:00:45
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pdxpug
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:

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 ...
INSERT INTO mytable ...

Let me know if you need more help.


Selena Deckelmann
United States PostgreSQL Association -
Me -

In response to

pdxpug by date

Next:From: Roth, GabrielleDate: 2008-04-10 15:02:14
Subject: April meeting in one week!
Previous:From: Thomas KellerDate: 2008-04-03 21:37:34
Subject: copy or update question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group