Re: BUG #3279: insert or update

steven(at)seefile(dot)com wrote:
> The following bug has been logged online:
>
> Bug reference: 3279
> Logged by:
> Email address: steven(at)seefile(dot)com
> PostgreSQL version: 8.1
> Operating system: macosx
> Description: insert or update
> Details:
>
> I sort of want to begin this with 'Hey, a--h---e.' I've got my code working
> for MySQL and then Sqlite and now I'm breaking my back on PostgreSQL. I'm
> not concerned with purity of code or algorithmic beauty or anything else.
> What I've got is I can't use the same standard query language queries on
> each database. Everybody does the standard different, which means it is
> broken. If I had started with MySQL, it would be broken, but instead it's
> Postgres I'm working last on, so it's PostgreSQL that is broken.
>
> You know by the short description what the problem is. I've read enough of
> your mail lists to know this issue has been brought up again and again. What
> I can't find yet is whether anybody has a work around. If you have, trigger
> function or some other c--p, why not just make it very public. Right now the
> only solution I have is try the INSERT, if it's an error, parse the query,
> find out what the primary key is (somehow), and rewrite it as update, which
> is a lot of work just so PostgreSQL can maintain it's idealic purity.

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
- example 37-1

the actual "standard" way to do that is by using MERGE with afaik
neither MySQL nor SQLite implement - but the correct solution is
documented and not really difficult to find.

Stefan