Re: Revisited: Transactions, insert unique.

davidb(at)vectormath(dot)com wrote:
>
> Hi Lincoln,
>
> I'm not sure I'm understanding your question, but it seems like this is
> something that
> ought to be handled programmatically. That is, query the table to see if
> the row exists,
> then decide what you are going to do (insert or update) based on the results
> of your
> query.

Good point. And you can combine the check David suggests with the insert
statement, e.g.,

INSERT INTO mytable (id, ...)
SELECT 7, ...
FROM mytable
WHERE NOT EXISTS (SELECT * FROM mytable WHERE id = 7)

And then check the return result for number of rows inserted. '0' means an
update is needed. I don't remember if there is cleaner more efficient
manner for doing that, but probably so...

Regards,
Ed Loehr

> David Boerwinkle
>
> -----Original Message-----
> From: Lincoln Yeoh <lylyeoh(at)mecomb(dot)com>
> To: pgsql-general(at)postgresql(dot)org <pgsql-general(at)postgresql(dot)org>
> Date: Monday, April 24, 2000 1:13 AM
> Subject: [GENERAL] Revisited: Transactions, insert unique.
>
> >Hi,
> >
> >Previously I wanted to ensure that I am inserting something unique into a
> >table, the answer was to create a unique index on the relevant columns.
> >
> >But what if I don't want to get an error which would force a rollback? Say
> >I want to insert something if it doesn't already exist, but update it if it
> >does.
> >
> >Do I have to lock the whole table?
> >
> >Would it be a good idea to be able to request a lock on an arbitrary string
> >like in MySQL? Then I could perhaps do something like
> >
> >LOCK HANDLE('max255charstring',TimeoutInSeconds)
> >e.g.
> >LOCK HANDLE('mytable,field1=x,field2=y',10)
> >
> >Then I could control access to a row that may not even exist, or do other
> >snazzy transaction stuff.
> >
> >Cheerio,
> >Link.
> >