Re: partitioned table

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: partitioned table
Дата
Msg-id CAHOFxGrp=T1QkBh6Lw-2zZqU=Lrno8kbnhwphc+cxKHFSZNaiQ@mail.gmail.com
обсуждение исходный текст
Ответ на partitioned table  (Pepe TD Vo <pepevo@yahoo.com>)
Ответы Re: [MASSMAIL]Re: partitioned table  (gilberto.castillo@etecsa.cu)
Re: [MASSMAIL]Re: partitioned table  (gilberto.castillo@etecsa.cu)
Список pgsql-admin
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."

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.

В списке pgsql-admin по дате отправления:

Предыдущее
От: Pepe TD Vo
Дата:
Сообщение: partitioned table
Следующее
От: gilberto.castillo@etecsa.cu
Дата:
Сообщение: Re: [MASSMAIL]Re: partitioned table