Re: partitioned table

--00000000000052cc89059bb8287f
Content-Type: text/plain; charset="UTF-8"

>
> when I manually mocking the data into both tables are fine and when I run
> the procedure, I get errorcode: 42P10 MSG: there is no unique or exclusion
> constraint matching on the CONFLICT specification
>
> the procedure is
>

...


> INSERT INTO ecisdrdm.bnft_curr_fact AS prod (bnft_fact_id,
> bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
> bene_cntry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id,
> actn_tm_in_id, src_sys_id,
> bnft_hist_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id,
> uscis_emp_id, application_id,
> rmtr_id, prpr_id, mig_filename)
> SELECT stg.bnft_fact_id, stg.bene_cntry_of_brth_id,
> stg.bene_cntry_of_rsdc_id,
> stg.bene_cntry_of_ctznshp_id, stg.frm_id, stg.svc_ctr_id,
> stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,
> stg.bnft_hist_actn_id, stg.bene_id, stg.bene_end_dt_id, stg.petnr_app_id,
> stg.atty_id, stg.uscis_emp_id, stg.application_id,
> stg.rmtr_id, stg.prpr_id, stg.mig_filename
> FROM ecisdrdm.stg_bnft_curr_fact stg
> ON CONFLICT ("bnft_fact_id") DO UPDATE
> SET (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsdc_id,
>


The documentation and the error message explain the issue.

"there is no unique or exclusion constraint matching on the CONFLICT
specification"

"The optional ON CONFLICT clause specifies an alternative action to raising
a unique violation or exclusion constraint violation error."
-https://www.postgresql.org/docs/current/sql-insert.html

You have an index, but it is not unique. With partitioning, you cannot
create a unique index on a column that is not contained by your partition
key. So, you need to re-write to skip the use of ON CONFLICT I expect.

--00000000000052cc89059bb8287f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quot=
e" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204)=
;padding-left:1ex"><div style=3D"font-family:verdana,helvetica,sans-serif;f=
ont-size:16px"><div dir=3D"ltr"><div dir=3D"ltr" style=3D"font-family:verda=
na,helvetica,sans-serif;font-size:16px">when I manually mocking the data in=
to both tables are fine and when I run the procedure, I get errorcode: 42P1=
0 MSG: there
is no unique or exclusion constraint matching on the CONFLICT specification=
</div><div dir=3D"ltr" style=3D"font-family:verdana,helvetica,sans-serif;fo=
nt-size:16px"><br></div><div dir=3D"ltr" style=3D"font-family:verdana,helve=
tica,sans-serif;font-size:16px">the procedure is=C2=A0</div></div></div></b=
lockquote><div><br>...</div><div>=C2=A0</div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div style=3D"font-family:verdana,helvetica,sans-serif;=
font-size:16px"><div dir=3D"ltr"><div dir=3D"ltr" style=3D"font-family:verd=
ana,helvetica,sans-serif;font-size:16px"><div><div>INSERT INTO ecisdrdm.bnf=
t_curr_fact AS prod (bnft_fact_id, bene_cntry_of_brth_id, bene_cntry_of_rsd=
c_id,=C2=A0</div><div><span style=3D"white-space:pre-wrap">	</span>bene_cnt=
ry_of_ctznshp_id, frm_id, svc_ctr_id, actn_dt_in_id, actn_tm_in_id, src_sys=
_id,=C2=A0</div><div><span style=3D"white-space:pre-wrap">	</span>bnft_hist=
_actn_id, bene_id, bene_end_dt_id, petnr_app_id, atty_id, uscis_emp_id, app=
lication_id,=C2=A0</div><div><span style=3D"white-space:pre-wrap">	</span>r=
mtr_id, prpr_id, mig_filename)</div><div>SELECT stg.bnft_fact_id, stg.bene_=
cntry_of_brth_id, stg.bene_cntry_of_rsdc_id,=C2=A0</div><div><span style=3D=
"white-space:pre-wrap">	</span>stg.bene_cntry_of_ctznshp_id, stg.frm_id, st=
g.svc_ctr_id, stg.actn_dt_in_id, stg.actn_tm_in_id, stg.src_sys_id,=C2=A0</=
div><div><span style=3D"white-space:pre-wrap">	</span>stg.bnft_hist_actn_id=
, stg.bene_id, stg.bene_end_dt_id, stg.petnr_app_id, stg.atty_id, stg.uscis=
_emp_id, stg.application_id,=C2=A0</div><div><span style=3D"white-space:pre=
-wrap">	</span>stg.rmtr_id, stg.prpr_id, stg.mig_filename</div><div>FROM ec=
isdrdm.stg_bnft_curr_fact stg</div><div>ON CONFLICT (&quot;bnft_fact_id&quo=
t;) DO UPDATE=C2=A0</div><div>SET (bnft_fact_id, bene_cntry_of_brth_id, ben=
e_cntry_of_rsdc_id,=C2=A0</div></div></div></div></div></blockquote><div><b=
r></div><div><br></div><div>The documentation and the error message explain=
 the issue.<br></div><div><br></div><div>&quot;there
is no unique or exclusion constraint matching on the CONFLICT specification=
&quot;<br></div><div><br></div><div></div><div>&quot;<span style=3D"color:r=
gb(13,10,11);font-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px"=
>The optional=C2=A0</span><code class=3D"gmail-literal" style=3D"box-sizing=
:border-box;font-family:monospace,monospace;font-size:14.4px;color:rgb(13,1=
0,11);word-break:break-word;border-radius:0.25rem;margin:0.6rem 0px;backgro=
und-color:rgb(248,249,250)">ON CONFLICT</code><span style=3D"color:rgb(13,1=
0,11);font-family:&quot;Open Sans&quot;,sans-serif;font-size:14.4px">=C2=A0=
clause specifies an alternative action to raising a unique violation or exc=
lusion constraint violation error.</span>&quot;<br></div><div>-<a href=3D"h=
ttps://www.postgresql.org/docs/current/sql-insert.html">https://www.postgre=
sql.org/docs/current/sql-insert.html</a>=C2=A0</div><div><br></div><div>You=
 have an index, but it is not unique. With partitioning, you cannot create =
a unique index on a column that is not contained by your partition key. So,=
 you need to re-write to skip the use of ON CONFLICT I expect.</div></div><=
/div>

--00000000000052cc89059bb8287f--