and my apologies for wasting your time.
After doing the first select I found that the software that uses this database
was alreading doing the inserts if there wasn't any row with a PK on it ...
so every UPDATE i did worked when I expected otherwise...
I only noticed that when I run the first SELECT and of course there it was the row i thought didn't exist ...
The UPDATE in Postgresql continues bugless :),
I think my mistake was making the wrong assumption about the software code and remembering the mysql REPLACE
lead me in the wrong way ... an simple select would make it clear that the aplication that uses
the DB was already doing the INSERT, shame on me :(
Thanks for everything :) Ruben Oliveira
Richard Huxton wrote:
Ruben Oliveira wrote:
I'm having an unexpected behavior from an UPDATE query :
I expected only updates to existing rows but it is INSERTING new rows when there isn't a PK
Hmm - doesn't sound likely. Especially if you don't supply a primary key.
There are no triggers,views or rules associated with the tables ....
and to make things worse I have a similar query to another table where the UPDATE works as expected
and if I want to make INSERTs I have to make them :)
It seems the UPDATE is working like the Mysql REPLACE ... I tested in Postgresql 7.3 in Linux and Postrgresql 8.0 in Win32
the new row in table084 has the field1,field2 from the where clause in the UPDATE
the default values for field3 ,field4 and the SET values from the UPDATE to field5, field6, field7
UPDATE table084 SET field5=table090.field9, field6=table090.field11, field7=date_o(now()) WHERE table084.field1=table088.field6 AND table084.field2=table090.field4 AND table088.field1='DOC_TITLE' AND table088.field2=123456 AND table088.field1=table090.field1 AND table088.field2=table090.field2;
You should really have a FROM clause in this update - I'd have thought you'd get an error with this in version 8.0
Could you show the actual problem:
1. BEGIN
2. SELECT oid,cmin,cmax,* FROM table084 WHERE <conditions returning no rows>
3. Run above query
4. SELECT oid,xmin,xmax,cmin,cmax,* FROM table084 WHERE <same conditions as above>
5. SELECT oid,xmin,xmax,cmin,cmax,* FROM table084 WHERE <row that has been updated properly>
6. ROLLBACK
That way we can see that a row has really been inserted and which transaction did it.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
