Re: funny update, say update 1, updated 1 added 2nd.

works fine for me. Do you have any triggers on the tables or other rules? Can you provide a complete SQL script that
starts from an empty database.

Jim

---------- Original Message -----------
From: Neil Dugan <postgres(at)butterflystitches(dot)com(dot)au>
To: pgsql-sql(at)postgresql(dot)org
Sent: Thu, 16 Jun 2005 13:38:58 +1000
Subject: [SQL] funny update, say update 1, updated 1 added 2nd.

> I have been having some trouble with a particular table view. An UPDATE
> command is not only changing the applicable record it is also creating a
> new record as well.
>
> wholesale=# select * from accounts_supplier;
> id | name | contact | addr | addr2 | town | postcode | state | phone | fax | account_type
> ----+------------------+---------+-----------+-------+----------------+----------+-------+-------+-----+--------------
> 1 | ABC construction | TOM | | | | | NSW | | | Cash Only
> 2 | test | | | | | | | | | 7 Day
> 3 | build-4-U | boss | somewhere | | back of beyond | | | | | 7 Day
> (3 rows)
>
> wholesale=# update accounts_supplier set addr='nowhere' where id=3;
> UPDATE 1
> wholesale=# select * from accounts_supplier;
> id | name | contact | addr | addr2 | town | postcode | state | phone | fax | account_type
> ----+------------------+---------+---------+-------+----------------+----------+-------+-------+-----+--------------
> 1 | ABC construction | TOM | | | | | NSW | | | Cash
> Only 2 | test | | | | | | | | | 7
> Day 6 | build-4-U | boss | nowhere | | back of beyond | | | | | 7
> Day 3 | build-4-U | boss | nowhere | | back of beyond | | | | | 7 Day
> (4 rows)
>
> Can anyone tell me why this is happening and how to fix it.
>
> Here are the table and view definitions.
>
> CREATE TABLE account_type (
> number smallint,
> name character varying(20)
> );
>
> CREATE TABLE address (
> addr character varying(40),
> addr2 character varying(40),
> town character varying(20),
> postcode character varying(10),
> state character(4)
> );
>
> CREATE TABLE supplier (
> id bigserial NOT NULL,
> name character varying(40),
> phone character varying(20),
> fax character varying(20),
> contact character varying(40),
> account_type smallint DEFAULT 0
> )
> INHERITS (address);
>
> CREATE VIEW accounts_supplier AS
> SELECT supplier.id,
> supplier.name,
> supplier.contact,
> supplier.addr,
> supplier.addr2,
> supplier.town,
> supplier.postcode,
> supplier.state,
> supplier.phone,
> supplier.fax,
> account_type.name AS account_type
> FROM supplier, account_type
> WHERE (account_type.number = supplier.account_type);
>
> CREATE RULE accounts_supplier_update
> AS ON UPDATE TO accounts_supplier
> DO INSTEAD UPDATE supplier
> SET name = new.name,
> contact = new.contact,
> addr = new.addr,
> addr2 = new.addr2,
> town = new.town,
> postcode = new.postcode,
> state = upper((new.state)::text),
> phone = new.phone,
> fax = new.fax,
> account_type = (SELECT account_type.number
> FROM account_type
> WHERE ((account_type.name)::text = (new.account_type)::text))
> WHERE (supplier.id = new.id);
>
> wholesale=# select version();
> version
> --------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 7.4.8 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.3 20050227 (Red
> Hat 3.4.3-22)
> (1 row)
>
> wholesale=# select * from account_type;
> number | name
> --------+-----------
> 0 | Cash Only
> 1 | 7 Day
> 2 | 30 Day
> 3 | 60 Day
> 4 | 90 Day
> (5 rows)
>
> Thanks for any help
> Regards Neil.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
------- End of Original Message -------