PostgreSQL INSERT INTO WITH multiple SELECT

Question

I'm looking for an SQL Statement to achieve the following:

I have a table that is like {ida | idb | count | direct}
I want to insert rows into the table taking the ids a and b from another table, count and direct are given values. I tried something like this:

Not Working:

INSERT INTO my_tbl (ida, idb, count, direct)  
SELECT id FROM other_tbl WHERE word ='test'  
UNION  
SELECT id FROM other_tbl WHERE word ='tost' 23, 5;

Thanks in advance.

Your insert columns must be match with select columns. In this sample u are trying to insert four columns with selecting one columns. — Kadir Çetintaş
– Kadir Çetintaş, Commented Jul 21, 2018 at 12:59
probably not, i tried to get this query working by try and error and this is where i decided to get help — Matthias Schroer
– Matthias Schroer, Commented Jul 21, 2018 at 13:02
@MatthiasSchroer . . . Sample data and desired results are usually a big help in conveying meaning. — Gordon Linoff
– Gordon Linoff, Commented Jul 21, 2018 at 13:04
@KadirÇetintaş i have some trouble getting to values from the same other_tbl with different wheres, is there a way to do this (and combine them with two given values)? — Matthias Schroer
– Matthias Schroer, Commented Jul 21, 2018 at 13:05

Gordon Linoff · Accepted Answer · 2018-07-21 13:03:55Z

3

Is this what you want?

INSERT INTO my_tbl (ida, idb, count, direct) 
    SELECT o1.id, o2.id, 23, 5
    FROM other_tbl o1 JOIN
         other_tbl o2
         ON o1.word = 'test' AND o2.word = 'tost';

answered Jul 21, 2018 at 13:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Matthias Schroer Over a year ago

yeah this is exactly what i was looking for, thanks!

Collectives™ on Stack Overflow

PostgreSQL INSERT INTO WITH multiple SELECT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related