Postgres lateral join refer to output row

Vote count:

0

I have the following setup:

CREATE TABLE A(id INT);
CREATE TABLE B(id INT);
CREATE TABLE C(aid INT, bid INT);

I would like to do the following:

INSERT INTO C SELECT A.id as aid, (SELECT B.id FROM B WHERE A.id=B.id) 
AS bid FROM A WHERE bid is not NULL;

However it gives an error that the column bid doesn't exist. So is there a way to refer to an output row in lateral join or make insert only in case bid is not null?

I would like to keep the lateral join though.

asked 1 min ago

iggy

36

Postgres lateral join refer to output row