The following bug has been logged online:
Bug reference: 3279
Logged by:
Email address: steven@seefile.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.
(Update then insert doesn't work for all databases because some databases
report zero rows updated unless a value is actually changed: updating a row
to the same current values reports zero rows updated. So that suggestion
requires that I create my own standardised query language and then translate
that to each database's peculiar SQL.)
