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  | [email protected]

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 = '[email protected]';

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
– jarlh, Commented Nov 18, 2015 at 21:50
"insert into a relational table" - all tables in a relational database are "relational". — user330315
– user330315, Commented Nov 18, 2015 at 21:51
I dont get the logick using union in this case , join or 2 subqueries is what you needed — LongBeard_Boldy
– LongBeard_Boldy, Commented Nov 18, 2015 at 22:06

Juan Carlos Oropeza · Accepted Answer · 2015-11-18 21:51:21Z

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 = '[email protected]'

answered Nov 18, 2015 at 21:51

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

Collectives™ on Stack Overflow

Postgres INSERT INTO with Two Select Statements

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related