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

Hi Tom

Thanks for the answer that explains things.

My i humbly suggest adding notes to the create  table clone ( original
including all).  Stating the limitation on constraints not duplicating
an identical name and code that depends on the "constraint name" aka
Upsert will fail.

Also suggest adding  the same comment to the Insert on conflict
stating.  Tables that are cloned with (including all ) the constraint
is not automatically recreated with the same name so the constraint
needs to be manually re-created with that specific name for the temp
table.
Additionally could throw a notice that when a table is cloned with the
copy indexes or constraints,  stating names are not preserved and may
cause issues with Upsert on conflict.

again thanks
On 7/28/2016 at 12:05 PM, "Tom Lane"  wrote:Vitaly Burovoy  writes:
> On 7/28/16, joel.traf@magwerks.com  wrote:
>> The documentation states "create temp table mytable (including all)
"
>> is suppose to create all the constraints

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

There's a comment in generateClonedIndexStmt about that:

  * We don't try to preserve the name of the source index; instead,
just
  * let DefineIndex() choose a reasonable name.

However, this isn't documented anywhere user-visible AFAICS, and it
probably should be.

It's rather annoying that LIKE doesn't duplicate names of constraints,
especially now that we've invented ON CONFLICT and thereby made
constraint
names something that would be explicitly referenced in DML code.
However,
I'm afraid that doing so is harder than it looks because index names
have
to be unique within a schema.  If we tried to copy constraint names
we'd
get a failure anytime the new table is in the same schema as the LIKE
source table.

 regards, tom lane