Search Postgresql Archives

Re: UPDATE Inserts New Rows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux