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;