Why update doesn't work with join based on returning values from insert on the same table

user_13329605 0
Viewed 44

I am updating a table with data from another table from another database. Most columns can be moved with no change but some of them require mapping/change after import since they refer to the new database ids. My first attempt was to make insert with returning values and use those values in update on the same table, with additional joins. It does not work. Postgres inserts records and ignores updates, with no error. Simplified example of the case below.

Create table test(
    idtst serial ,
    name varchar(10),
    nameid varchar(20));
    
with tst as ( -- makes insert, no update
Insert into test(name,nameid) values ('tsta','tsta')
    returning idtst,nameid)
update test t
set nameid=t.nameid||t.idtst::varchar(5)
from tst i
where t.idtst=i.idtst;

with tst as ( -- makes insert, no update
Insert into test(name,nameid) values ('tstb','tstb')
    returning idtst,nameid)
update test t
set nameid=i.nameid||i.idtst::varchar(5)
from tst i
where t.idtst=i.idtst;

Create temp table tmp_tst(
    idtst integer,nameid varchar(20));

with tst as (
Insert into test(name,nameid) values ('tstc','tstc')
    returning idtst,nameid)
Insert into tmp_tst Select * from tst; -- insert into tmp
update test t -- makes update
set nameid=i.nameid||i.idtst::varchar(5)
from tmp_tst i
where t.idtst=i.idtst;

Does it depend on postgres version/configuration ? In test in result there are records

1,tsta,tsta
2,tstb,tstb
3,tstc,tstc3

No errors, no warnings, just psql ignores first two updates. Updates/Inserts on any other table are OK.

postgresql
1 Answers

What is table i? Where does it come from?

My guess would be, that there is no lines which fullfill t.idtst=i.idtst

Maybe you should look at this:

SELECT *
FROM test as t, i
WHERE t.idtst=i.idtst
user_181806440
Related