Update each row with a new entity

Question

I have two tables:

CREATE TABLE public.test
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY
)

and

CREATE TABLE public.test2
(
    id integer,
    test_id integer
)

Table test2 has two rows (1, null) and (2, null). Table test has nothing. Now I want to fill test_id by creating new rows in test. I nead a new entity each time so that I will have (1, 1), (2, 2), etc. I try to prepare update query with an insert statement but I don't understand how to do it. This is what I try:

update t2 set t2.test_id = t.id
from test2 t2 full join (INSERT INTO test(id) VALUES (default) RETURNING id) t on t2.test_id = t.id

but I get the following:

ERROR:  syntax error at or near "INTO"
LINE 2: from test2 t2 full join (INSERT INTO test(id) VALUES (defaul...
                                        ^
SQL state: 42601
Character: 65

Can I create the query I want somehow?

Do you really have just one column in `test`, or there are others? — GMB, Sep 07 '20 at 10:15

score 1 · Accepted Answer · answered Sep 07 '20 at 10:36

Having just one column in the target table makes things a little tricky. It might be simpler to generate and assign the new ids first, using next_val, and then insert the values in test (we need option overriding system value to insert into a generated always colum,)

with t2 as (
    update test2
    set test_id = nextval('test_id_seq')
    where test_id is null
    returning test_id
) 
insert into test(id) overriding system value 
select test_id from t2

Demo on DB Fiddlde

Update each row with a new entity

1 Answers1