sql - Postgres lateral join 引用输出行

Question

我有以下设置:

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

我想做以下事情:

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;

但是它给出了列出价不存在的错误。那么有没有办法在横向连接中引用输出行或仅在 bid 不为空的情况下进行插入？

不过我想保留横向连接。

Answer 1

你不能像那样引用别名，你需要包装选择:

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;