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
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;
where table084 is defined as :
my2005=# \d table084
Table "public.table084"
Column | Type | Modifiers
--------+---------------+---------------------------
field1 | integer | not null default 0
field2 | text | not null default ''::text
field3 | text | not null default ''::text
field4 | text | not null default ''::text
field5 | numeric(16,3) | not null default 0.000
field6 | numeric(16,3) | not null default 0.000
field7 | text | not null default ''::text
Indexes:
"table084_pkey" PRIMARY KEY, btree (field1, field2)
Am I missing something ?
I can give you more details if needed.
Thanks for any help ...
Ruben Oliveira
