The following bug has been logged online:

Bug reference: 1730
Logged by: Krasimir dimitrov
Email address: kr (AT) aiidatapro (DOT) com
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_compan y_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 (AT) postgresql (DOT) org

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