PostgreSQL INSERT – From SELECT Combined with Single Value

insertpostgresqlpostgresql-9.1

I have this query that returns several rows of item_id: 

select item_id from properties where name like 'body';

And I have the second one that returns 1 row of tag_id:

select id from tags where name ilike '%hoax%';

I want to use both results to create new rows in a third table item_tags so that I can insert each item_id from the first query with the tag_id from the second query.

I'll have something like: 

INSERT INTO item_tags (item_id, tag_id) VALUES (item_id1, tag_id);  
INSERT INTO item_tags (item_id, tag_id) VALUES (item_id2, tag_id);
INSERT INTO item_tags (item_id, tag_id) VALUES (item_id3, tag_id);   
 ...

The 1st query returns over 800 rows. How can I automate the insert?
psql 9.1.11, Postgres 9.1.4.

Best Answer

Basic INSERT

INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM  (SELECT item_id FROM properties WHERE name LIKE 'body') p
    , (SELECT id FROM tags WHERE name ILIKE '%hoax%') t

Where the comma (,) in the FROM clause is short notation for CROSS JOIN.

That's building on your assertion that the second query on tags ...

returns 1 row of tag_id

You get a Cartesian Product from a CROSS JOIN, that's n*m combinations.
If both sides of the join return more than one row, they multiply each other.
If either side does not return a row, the overall result is no row and nothing happens.
If you want neither of those effects, one side must return at least one row, and the other side exactly one row.

For your specific combination you can also use a nested subselect:

INSERT INTO item_tags (item_id, tag_id)
SELECT item_id 
     , (SELECT id FROM tags WHERE name ILIKE '%hoax%') AS tag_id  -- single result!
FROM   properties
WHERE  name LIKE 'body';

The subtle difference: This inserts all rows returned from the outer SELECT, even if the subselect on tags returns no row, which would be converted to NULL for tag_id.

Bulk INSERT

To deal with lots of such inserts at once, you would use a largely modified query. But the same rules as for the first query above apply for cardinalities in both joined tables.

INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM  (
   VALUES
      ('body',  'hoax')
    , ('body2', 'fun')
    , ('body3', 'love')
  ) AS it(item, tag)
JOIN   properties p ON p.name LIKE it.item
JOIN   tags t ON t.name ILIKE ('%' || it.tag ||'%');

You see the logic, right? Personally, I call this a "CROSS JOIN by proxy".

Related Question