bug error message constraint "con_item_id_costelement" for table "costs" does not exist SQL state: 42704
От | joel.traf@magwerks.com |
---|---|
Тема | bug error message constraint "con_item_id_costelement" for table "costs" does not exist SQL state: 42704 |
Дата | |
Msg-id | 20160728151154.AABE64016B@smtp.hushmail.com обсуждение исходный текст |
Ответы |
Re: bug error message constraint "con_item_id_costelement" for
table "costs" does not exist SQL state: 42704
|
Список | pgsql-bugs |
Hello PostgreSQL Developers Ran into a bug or issue with the documentation where creating a TEMP table fails to create all the constraints and Upsert fails on check constraint The documentation states "create temp table mytable (including all) " is suppose to create all the constraints https://www.postgresql.org/docs/9.5/static/sql-createtable.html below is the the code to duplicate this issue. I was able to work around the issue by adding the needed constraint below the create temp command. select version() result: "PostgreSQL 9.5.0, compiled by Visual C++ build 1800, 64-bit" ---code to create error. CREATE TABLE xmag.costs ( cost_id serial primary key, cost_item_id integer NOT NULL, cost_costelem_id integer NOT NULL, cost_lowlevel boolean NOT NULL DEFAULT false, cost_stdcost numeric(24,8) NOT NULL DEFAULT 0, cost_posted date, cost_actcost numeric(24,8) NOT NULL DEFAULT 0, cost_updated date, CONSTRAINT con_item_id_costelement UNIQUE (cost_item_id, cost_costelem_id) ); insert into xmag.costs values (12083,12210,3,FALSE,16.95000000,'2014-03-25',16.95000000,'2014-03-25'), (12084,12211,3,FALSE,6.27000000,'2014-03-25',6.27000000,'2014-03-25'), (12085,12212,3,FALSE,35.36000000,'2014-03-25',35.36000000,'2014-03-25'), (12088,12215,3,FALSE,31.50000000,'2013-10-25',31.50000000,'2014-07-31'), (12089,12216,3,FALSE,0.64000000,'2013-06-27',0.64000000,'2014-07-31'), (12090,12217,3,FALSE,0.00000000,'2013-06-27',0.00000000,'2014-07-31'); create temp table costs ( like xmag.costs including all ); --commented out to show bug --ALTER TABLE public.itemcost --ADD CONSTRAINT con_item_id_itemcostelement UNIQUE(itemcost_item_id, itemcost_costelem_id); insert into costs (select * from costs); Insert into costs values (default,12210,3,FALSE,16.95000000,'2014-03-25',16.95000000,'2014-03-25') on conflict on constraint con_item_id_costelement do update set cost_actcost = matcost, cost_updated = now()::Date, cost_posted = now()::date, cost_stdcost = 10 where cost_item_id= 12210 and costs.cost_costelem_id = 3;
В списке pgsql-bugs по дате отправления: