[BUGS] BUG #1730: insert into x1.tbl select x2.tbl dont work

[Krasimir dimitrov]

The following bug has been logged online:

Bug reference:      1730
Logged by:          Krasimir dimitrov
Email address:      [EMAIL PROTECTED]
PostgreSQL version: 7.4
Operating system:   Debian Linux
Description:        insert into x1.tbl select x2.tbl  dont work
Details: 

Hello there,
I use postgresql-7.4(7.4.8-9) on debian with kernel 2.6.11.2

My idea is: when delete from table cms.news trigger insert data in
archive.news I found a bug in Postgres-7.4.

before running:
 delete  from cms.news where
uid_news='118dd5bd-19be-416f-bf68-f9682147184c';

data in table cms.code_company is:

aiidatapro.net=# select * from cms.code_company;
 id_company_coded |            uid_news_coded            |
fk_id_base_company_coded
------------------+--------------------------------------+------------------
--------
                2 | 118dd5bd-19be-416f-bf68-f9682147184c |                  
     2
                3 | 118dd5bd-19be-416f-bf68-f9682147184c |                  
     3
(2 rows)

                

Bug is appear when run

insert into archive.code_company  select * from cms.code_company WHERE
uid_news_coded=uid;

in my trigger "on delete" in table cms.news
result is:

aiidatapro.net=# select * from cms.code_company;
 id_company_coded |            uid_news_coded            |
fk_id_base_company_coded
------------------+--------------------------------------+------------------
--------
                2 | 118dd5bd-19be-416f-bf68-f9682147184c |                  
     2
                3 | 118dd5bd-19be-416f-bf68-f9682147184c |                  
     3
                2 | 118dd5bd-19be-416f-bf68-f9682147184c |                  
     2
                3 | 118dd5bd-19be-416f-bf68-f9682147184c |                  
     3

but I set unique constraint :

ALTER TABLE cms.code_company   ADD CONSTRAINT
code_company_uid_news_coded_key UNIQUE(uid_news_coded,
fk_id_base_company_coded);

This couldn't be happend !!!!!

constraint works when insert:

aiidatapro.net=# insert into cms.code_company
(id_company_coded,uid_news_coded,fk_id_base_company_coded) values
(2,'118dd5bd-19be-416f-bf68-f9682147184c',2);
ERROR:  duplicate key violates unique constraint "code_company_pkey"


please check query :

insert  into x1.tbl  select from x2.tbl

tanks

p.s. I send copy of this mail to pgsql-bugs@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend