5

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.

  • 3
    You can use INSERT INTO item_tags (item_id, tag_id) SELECT ... Just write the appropriate select query that combines the two you have into a 2 column select. – ypercubeᵀᴹ May 21 '14 at 14:39
  • thanks. If you answer the question i can vote for it. here is what i did INSERT INTO item_tags (item_id, tag_id) select * from (select item_id from properties where name like 'body') a, ( select id as tag_id from tags where name ilike '%hoax%') b; – mamesaye May 21 '14 at 17:41
  • Please note that if you have 100 item_ids and 200 tag_ids, you will be inserting 100*200 records. – Jayadevan May 22 '14 at 5:50
  • Aside: Why the version mismatch between psql and Postgres? It's recommended that you always upgrade to the latest point release of the major version in use - and that's currently 9.1.13 for 9.1. – Erwin Brandstetter May 26 '14 at 1:09
6

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".

  • I love the "cross join by proxy" from today, it executes our 160000 rows sql under one second. On pg 10 it took 8 seconds, and on pg 11 it crashed the server on out of memory (it used nested subselect) after 8 seconds. Cross join by proxy is fast, readable and completely beautiful! :-) – dmatej Mar 6 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.