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

Hi Vitaly

In the code below i changed things around shorten names and created
code to create the error. I failed to rename the constraint to match
all the other changes before submitting the bug.

When i ran into this bug it took a bit of digging because several
plpgsql functions are being called and this is just one table that a
temp table is created to run "what if's" based on user input.

Once the transaction ends it discards all the temp tables which are
clones of real data.

Also ran into another potential bug that i have not written code to
create the error Run the below command inside a PLpgsql function it
fails stating the table can not be altered as its in use in another
transaction. its a temp table created by the function ;-/

create temp table costs ( like xmag.costs including all );
ALTER TABLE public.itemcost
ADD CONSTRAINT con_item_id_itemcostelement UNIQUE(itemcost_item_id,
itemcost_costelem_id);
The solution was to move the Temp Table creation to the client side
before all the plpgsql functions are called.
I went to using Upsert command because it simplified code and to
speed up a function that uses it.

To get the Upsert to work i had to add this constraint, to a table
that has 5 other constraints that are not included in the below code.

Thanks
On 7/28/2016 at 11:43 AM, "Vitaly Burovoy" wrote:On 7/28/16,
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