Postgres - fill in missing data in new table

Question

Given two tables, A and B:

A            B
-----        -----
id           id
high         high
low          low
bId

I want to find rows in table A where bId is null, create an entry in B based off the data in A, and update the row in A to reference the newly created row. I can create the rows but I'm having trouble updating table A with the reference to the new row:

begin transaction;

with rows as (
    insert into B (high, low)
    select high, low
    from A a
    where a.bId is null
    returning id as bId, a.id as aId
)
update A
set bId=(select bId from rows where id=rows.aId)
where id=rows.aId;

--commit;
rollback;

However, this fails with a cryptic error: ERROR: missing FROM-clause entry for table a.

Using a Postgres query, how can I achieve this?

Start by not using `rows` as an identifier. (and:I would avoid the scalar subquery, too) — wildplasser, Jun 05 '19 at 21:30
Also, column identifiers are case insensitive. Best to use lower case: https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive. — Mark McKelvy, Jun 05 '19 at 21:49

score 0 · Answer 1 · answered Jun 06 '19 at 13:44

0

either

update "A"
set "bId"=(select "bId" from rows where id=rows."aId")

without the where clause or

update "A"
set "bId"=(select "bId" from rows where id=rows."aId")
FROM rows
where "A".id=rows.aId;

I dont know if your tables realy have that names, as mentioned in the comments try to avoid uppercase tables and fieldnames and try to avoid reserved keynames.

answered Jun 06 '19 at 13:44

FatFreddy

1,160
1
9
16

Thanks, the table names aren't really like that, I simplified the names and columns simply for example purposes. – riqitang Jun 06 '19 at 14:21

riqitang · Answer 2 · 2019-06-06T17:37:37.527

I found a way to get it to work but I feel like it's not the most efficient.

begin transaction;
do $body$
declare 
    newId int4;
    tempB record;
begin
create temp table TempAB (
    High float8,
    Low float8,
    AID int4
);
insert into TempAB (High, Low, AId)
select high, low, id
from A
where bId is null;

for tempB in (select * from TempAB)
loop
    insert into B (high, low)
    values (tempB.high, tempB.low)
    returning id into newId;
    update A
    set bId=newId
    where id=tempB.AId;
end loop;
end $body$;
rollback;
--commit;

Postgres - fill in missing data in new table

2 Answers2