Обсуждение: BUG #3279: insert or update
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.)
steven@seefile.com wrote: > 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. 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