bug error message constraint "con_item_id_costelement" for table "costs" does not exist SQL state: 42704

From: joel(dot)traf(at)magwerks(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: bug error message constraint "con_item_id_costelement" for table "costs" does not exist SQL state: 42704
Date: 2016-07-28 15:11:54
Message-ID: 20160728151154.AABE64016B@smtp.hushmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Vitaly Burovoy 2016-07-28 15:43:00 Re: bug error message constraint "con_item_id_costelement" for table "costs" does not exist SQL state: 42704
Previous Message David G. Johnston 2016-07-28 13:56:34 Re: BUG #14268: NULL parameter conversion