Thanks for the answer Richard
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
>
