Postgres INSERT INTO with Two Select Statements

Question

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

table 1:

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

table 2:

id | address
----------------------------
1  | something@something.com

table 3:

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

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

I've been working with this:

INSERT INTO table3
SELECT id FROM table1
WHERE table1.url = 'http://something.com'
UNION
SELECT id FROM table2
WHERE table2.address = 'something@something.com';

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.

UNION ALL keeps duplicates, but that's not the answer. You need a CROSS JOIN. — jarlh, Nov 18 '15 at 21:50
"*insert into a relational table*" - all tables in a relational database are "relational". — , Nov 18 '15 at 21:51
I dont get the logick using union in this case , join or 2 subqueries is what you needed — LongBeard_Boldy, Nov 18 '15 at 22:06

score 1 · Accepted Answer · answered Nov 18 '15 at 21:51

1

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 = 'something@something.com'

answered Nov 18 '15 at 21:51

Juan Carlos Oropeza

47,252
12
78
118

Postgres INSERT INTO with Two Select Statements

1 Answers1

Linked