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

On 7/28/16, joel(dot)traf(at)magwerks(dot)com <joel(dot)traf(at)magwerks(dot)com> wrote:
> 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);

You have there columns which are present neither in xmag.costs nor in costs.
CREATE TABLE ... (LIKE ...)
creates indexes and constraints with names as if they weren't given
(i.e. by default), so your
constraint "con_item_id_costelement" becomes
"costs_cost_item_id_cost_costelem_id_key"

> insert into costs (select * from costs);

The command above does nothing since "costs" has just been creates and
has nothing.

> 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

Here you can use columns of index, e.g:
ON CONFLICT (cost_item_id, cost_costelem_id) DO UPDATE

> 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;
>

--
Best regards,
Vitaly Burovoy