使用两个Select语句的Postgres INSERT INTO [英] Postgres INSERT INTO with Two Select Statements

问题描述

我正尝试将两个SELECT查询的结果插入到关系表中。

I'm trying to insert into a relational table with the result of two SELECT queries.

表1：

id | url
-------------------------
1  | http://something.com

表2：

id | address
----------------------------
1  | [e-mail]

表3：

table1_id | table2_id
---------------------
1         | 1

我正在尝试构建由两个SELECT和UNION组合而成的INSERT查询

And I'm trying to build a INSERT query combined out of two SELECTs and a UNION

我一直在使用它：

INSERT INTO table3
SELECT id FROM table1
WHERE table1.url = 'http://something.com'
UNION
SELECT id FROM table2
WHERE table2.address = '[e-mail]';

我在哪里错了？我在第二个SELECT上遇到错误，它能够选择第一个ID并将其传递给INSERT，但是即使第二个SELECT本身有效，它仍试图插入（1，null）。

Where have I gone wrong with this? I'm getting an error on the second SELECT, it is able to SELECT the first ID and pass it into the INSERT, but it's trying to insert (1, null) even though the second SELECT is valid on its own.

推荐答案

INSERT INTO table 3
    select A.id, B.id
    from Table1 A 
    cross join Table2 B
    where A.url = 'http://something.com'
      and B.address = '[e-mail]'

这篇关于使用两个Select语句的Postgres INSERT INTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！