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.

Answer 1

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';