Upsert in PostgreSQL

Question

I'm trying to do the upsert in a table with the fields of the another table, I trying to use the On Conflict "the code below" but it give me always this error: "Errore SQL [42601]: ERROR: syntax error at or near "from" Posizione: 926" but if I going to remove the "from orchestrate orchestrate" it going to give me this exception :"Errore SQL [42P01]: ERROR: missing FROM-clause entry for table "orchestrate" Posizione: 334" can somebody help me?

insert into ro_banche (id_banca,code_abi, code_fisc, desc_banca, desc_banca_rid, data_attivaz, data_cessaz, code_abi_nuovo, code_abi_trasf, code_abi_cor, code_abi_nocor, code_abi_giro, flag_tipo_ades, code_tipo_istituto, data_modifica, utente_modifica)
select * from orchestrate 
ON CONFLICT (code_abi) DO 
update set code_fisc = orchestrate.code_fisc, desc_banca = orchestrate.desc_banca, desc_banca_rid = orchestrate.desc_banca_rid, data_attivaz = orchestrate.data_attivaz, data_cessaz = orchestrate.data_cessaz, code_abi_nuovo = orchestrate.code_abi_nuovo, code_abi_trasf = orchestrate.code_abi_trasf, code_abi_cor = orchestrate.code_abi_cor, code_abi_nocor = orchestrate.code_abi_nocor, code_abi_giro = orchestrate.code_abi_giro, flag_tipo_ades = orchestrate.flag_tipo_ades, code_tipo_istituto = orchestrate.code_tipo_istituto, data_modifica = orchestrate.data_modifica, utente_modifica = orchestrate.utente_modifica
from orchestrate orchestrate
where (ro.code_abi = orchestrate.code_abi);

Why do you write `orchestrate` twice in the FROM clause ? Only once should be ok. — Edouard, Dec 27 '21 at 17:30
You can’t use “select *”, you need to list the columns explicitly and in an order that matches the column list for the target you’ve defined. Otherwise, how is your process going to know which columns from your source need to be inserted into which columns in your target? — NickW, Dec 27 '21 at 17:33

LukStorms · Accepted Answer · 2021-12-28T14:21:46.003

The extra FROM isn't required in the syntax.

Since that update is on record level, for those in conflict because of duplicate.

And you want to update from the excluded

INSERT INTO ro_banche
(id_banca, code_abi, code_fisc, desc_banca, desc_banca_rid, data_attivaz, data_cessaz, code_abi_nuovo, code_abi_trasf, code_abi_cor, code_abi_nocor, code_abi_giro, flag_tipo_ades, code_tipo_istituto, data_modifica, utente_modifica)
SELECT 
 id_banca, code_abi, code_fisc, desc_banca, desc_banca_rid, data_attivaz, data_cessaz, code_abi_nuovo, code_abi_trasf, code_abi_cor, code_abi_nocor, code_abi_giro, flag_tipo_ades, code_tipo_istituto, data_modifica, utente_modifica
FROM orchestrate 
ON CONFLICT (code_abi) DO 
UPDATE SET 
  code_fisc = excluded.code_fisc
, desc_banca = excluded.desc_banca
, desc_banca_rid = excluded.desc_banca_rid
, data_attivaz = excluded.data_attivaz
, data_cessaz = excluded.data_cessaz
, code_abi_nuovo = excluded.code_abi_nuovo
, code_abi_trasf = excluded.code_abi_trasf
, code_abi_cor = excluded.code_abi_cor
, code_abi_nocor = excluded.code_abi_nocor
, code_abi_giro = excluded.code_abi_giro
, flag_tipo_ades = excluded.flag_tipo_ades
, code_tipo_istituto = excluded.code_tipo_istituto
, data_modifica = excluded.data_modifica
, utente_modifica = excluded.utente_modifica;

Simplified test on db<>fiddle here

insert into test1 (num, col)
select * 
from test2 
where col < 9
on conflict (num) do
update set col = excluded.col

Simplified test 2 (no primary key) on db<>fiddle here

do $$
begin 
  update test1 t1 
  set col = t2.col
  from test2 t2 
  where t2.num = t1.num;
  
  insert into test1 (num, col)
  select num, col
  from test2 t2
  where not exists (
    select 1
    from test1 t1
    where t1.num = t2.num
  );
end $$;

Thanks so much, just last question, if I don't have a field unique but i wanna ckìheck if the same value (of the whole row) is the same of the another row, what shoud I write in the conflict cause? — Robs, Dec 28 '21 at 13:33
Hmm. The CONFLICT syntax does expect that the matching field is either a primary key or has a unique index. I've added an example to the solution for when that's not the case. Based on [this SO post](https://stackoverflow.com/a/6527838/4003419). — LukStorms, Dec 28 '21 at 14:08
Do note however that if you use the update & insert method that it's best to do it on a combination of fields that are unique in the table. Or add a unique index if that's possible. — LukStorms, Dec 28 '21 at 14:18

score 0 · Answer 2 · answered Jul 07 '22 at 14:31

insert into ro_banche (id_banca,code_abi, code_fisc, desc_banca, desc_banca_rid, data_attivaz, data_cessaz, code_abi_nuovo, code_abi_trasf, code_abi_cor, code_abi_nocor, code_abi_giro, flag_tipo_ades, code_tipo_istituto, data_modifica, utente_modifica) select * from orchestrate ON CONFLICT (code_abi) DO update set code_fisc = orchestrate.code_fisc, desc_banca = orchestrate.desc_banca, desc_banca_rid = orchestrate.desc_banca_rid, data_attivaz = orchestrate.data_attivaz, data_cessaz = orchestrate.data_cessaz, code_abi_nuovo = orchestrate.code_abi_nuovo, code_abi_trasf = orchestrate.code_abi_trasf, code_abi_cor = orchestrate.code_abi_cor, code_abi_nocor = orchestrate.code_abi_nocor, code_abi_giro = orchestrate.code_abi_giro, flag_tipo_ades = orchestrate.flag_tipo_ades, code_tipo_istituto = orchestrate.code_tipo_istituto, data_modifica = orchestrate.data_modifica, utente_modifica = orchestrate.utente_modifica from orchestrate orchestrate where (ro.code_abi = orchestrate.code_abi);

so, this query didn't worked.

Try with one given below:

INSERT INTO ro_banche (
    id_banca,
    code_abi,
    code_fisc, 
    desc_banca,
    desc_banca_rid,
    data_attivaz,
    data_cessaz,
    code_abi_nuovo,
    code_abi_trasf,
    code_abi_cor,
    code_abi_nocor,
    code_abi_giro,
    flag_tipo_ades,
    code_tipo_istituto,
    data_modifica,
    utente_modifica
) 
SELECT * FROM orchestrate 
WHERE code_abi IN (SELECT code_abi FROM ro_banche) 
ON CONFLICT (code_abi) DO UPDATE SET (
    code_fisc = excluded.code_fisc,
    desc_banca = excluded.desc_banca,
    desc_banca_rid = excluded.desc_banca_rid,
    data_attivaz = excluded.data_attivaz,
    data_cessaz = excluded.data_cessaz,
    code_abi_nuovo = excluded.code_abi_nuovo,
    code_abi_trasf = excluded.code_abi_trasf,
    code_abi_cor = excluded.code_abi_cor,
    code_abi_nocor = excluded.code_abi_nocor,
    code_abi_giro = excluded.code_abi_giro,
    flag_tipo_ades = excluded.flag_tipo_ades,
    code_tipo_istituto = excluded.code_tipo_istituto,
    data_modifica = excluded.data_modifica,
    utente_modifica = excluded.utente_modifica
);

Upsert in PostgreSQL

2 Answers2