Postgres lateral join refer to output row

Question

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.

user330315 · Accepted Answer · 2015-03-04 10:30:11Z

2

You can't reference the alias like that, you need to wrap the select:

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

answered Mar 4, 2015 at 10:30

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres lateral join refer to output row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related