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 7: don't forget to increase your free space map settings
