Re: Revisited: Transactions, insert unique.

davidb@vectormath.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@mecomb.com>
> To: pgsql-general@postgresql.org <pgsql-general@postgresql.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.
> >