0

I have this query:

DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table(id int primary key) 
IGNORE (
   SELECT user2role.userid AS userid
     FROM user2role 
    INNER JOIN users ON users.id=user2role.userid 
    INNER JOIN role ON role.roleid=user2role.roleid 
    WHERE role.parentrole like 'H1::H2::H3::H4::H5::%') 
UNION (
   SELECT groupid
     FROM groups
    WHERE groupid IN (2,3,4));

This query was originally written in MySQL and instead of DROP TABLE IF EXISTS it used IF NOT EXISTS. I changed that part, but I don't know what to do about the IGNORE.

First off, what is IGNORE doing?

I tried looking for PostgreSQL equivalents, but they all seem to involve complicated procedures. Do I have to write a procedure for this? And if I have to write one, what would it look like? Could I just emulate IGNORE using some PHP code instead? (The SQL queries are generated by PHP.)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
zermy
  • 611
  • 1
  • 11
  • 25

1 Answers1

6

You would write like this in postgres.
IGNORE is irrelevant here, as the table has just been recreated and is guaranteed to be empty. And UNION guarantees there are no duplicate rows inserted.

DROP TABLE IF EXISTS tmp_table;

CREATE TEMP TABLE tmp_table(id int4 primary key);

INSERT INTO tmp_table
SELECT user2role.userid::int4 AS id
  FROM user2role 
  JOIN users ON users.id = user2role.userid 
  JOIN role ON role.roleid = user2role.roleid 
 WHERE role.parentrole like 'H1::H2::H3::H4::H5::%'
UNION
SELECT groupid::int4
  FROM groups
 WHERE groupid in (2,3,4);

If duplicates in the SELECT cannot occur, you might consider the faster UNION ALL instead of UNION. Otherwise you need UNION to eliminate possible dupes. Read here.
If your dataset is large you might consider creating the primary key after the INSERT. That's faster.

Read the mySQL docs on effects of IGNORE.

On revisiting the page I realized you mention IF NOT EXISTS in the original code. You don't say so, but that only makes sense if the original code created the table only if it didn't exist already, which introduces the possibility of it being not empty before the INSERT. In this case IGNORE is relevant and needs an equivalent in PostgreSQL.

So here is alternative answer for that interpretation of your question.

CREATE TEMP TABLE IF NOT EXISTS has been implemented in PostgreSQL 9.1.
For older version I posted a solution on SO recently.

CREATE TEMP TABLE IF NOT EXISTS tmp_table(id int4 primary key);

INSERT INTO tmp_table
SELECT x.id
  FROM (
    SELECT user2role.userid::int4 AS id
      FROM user2role 
      JOIN users ON users.id = user2role.userid 
      JOIN role ON role.roleid = user2role.roleid 
     WHERE role.parentrole like 'H1::H2::H3::H4::H5::%'
    UNION
    SELECT groupid::int4
      FROM groups
     WHERE groupid in (2,3,4)
        ) x
  LEFT JOIN tmp_table t USING (id)
 WHERE t.id IS NULL;

LEFT JOIN ... WHERE t.id IS NULL excludes any id that might already be present in tmp_table. UNION goes into a sub-select, so that clause needs only be applied once. Should be fastest.
More on LEFT JOIN here.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Oh, so the the stuff following ignore is just inserted into the new temp table? In that case why use ignore instead of insert into? – zermy Sep 28 '11 at 17:00
  • I would have to guess. It may have been useful in mySQL, but is not needed (and does not exist) in postgres. There are side-effect of the keyword `IGNORE` like treating duplicate-key-errors as just warning. But that cannot occur here, as `UNION` per definition eliminates duplicates. – Erwin Brandstetter Sep 28 '11 at 17:12
  • Thanks a lot, 2 quick questions, why did you change int to int4 and Inner JOIN to JOIN (is it because Join is Inner by default, so it doesn't matter?)? – zermy Sep 28 '11 at 17:50
  • `INNER JOIN` and `JOIN` are [synonymous in postgres](http://www.postgresql.org/docs/9.1/interactive/sql-select.html). I prefer the short version. Added the cast `::int4` to be sure, because the question does not tell us the type `userid`and `groupid`. If those are integer already, you can leave that out. – Erwin Brandstetter Sep 28 '11 at 18:07
  • I rejected an edit, because this is correct: "If there cannot be duplicates in the SELECT, you might consider UNION ALL". Only if it is guaranteed that there are no duplicates to begin with, then you can make use of the faster `UNION ALL`. Else you have to use `UNION` to eliminate dupes. I clarified the text. – Erwin Brandstetter Sep 28 '11 at 19:49
  • Thanks so much, I had just run into problems because I was using `DROP TABLE IF EXISTS`. Now, the only issue is that in order to do this, I am going to have to upgrade from 9.0 to 9.1. Any tips on making a smooth upgrade? – zermy Oct 11 '11 at 14:22
  • @zermy: you are aware that `DROP TABLE IF EXISTS` is 9.0 and 8.4 as well? `CREATE TEMP TABLE IF NOT EXISTS` is new in 9.1 and there are alternatives for older versions. As for upgrading: Most of my servers are running on 9.0 still. I am looking into an upgrading them myself, now that debian has backports for squeeze. follow the offizial instructions and it should be easy as pie. – Erwin Brandstetter Oct 11 '11 at 15:11
  • Yeah, I knew `DROP TABLE IF EXISTS` existed in 9.0, I was using it, and I was running into problems because information from an empty table was being displayed. Anyways, thanks, my only gripe with updating is that I need to dump and restore (most likely), so, I am just worried that something will go wrong, but oh well, for now, I am only upgrading my local machine, so it shouldn't be a big deal. – zermy Oct 11 '11 at 15:19
  • @zermy: There is also [pg_upgrade](http://www.postgresql.org/docs/current/interactive/pgupgrade.html). – Erwin Brandstetter Oct 11 '11 at 19:40
  • I am really sorry to bother you again, but could you just clarify something, quickly for me when you get the chance. What is `x.id`? And how does the `LEFT JOIN` bit work? The thing is, currently, it is complaining that the column `t.tmp_table` does not exist. Where does the t come from? Is it a stand in for something? Thanks for everything, you have helped me a lot. – zermy Oct 12 '11 at 20:33
  • @zermy: `x.id` is `user2role.userid::int4 AS id` from the subselect named `x`. If `userid` is of type integer, you can simplify to: `user2role.userid AS id`. Last line in the 2nd query had a mistake. Fixed it, should work now - must be `t.id`, not `t.tmp_table`. Read about `LEFT JOIN` in the manual, I added a link to my answer. It is simple, really. – Erwin Brandstetter Oct 12 '11 at 21:59
  • @zerny: the "LEFT JOIN ... WHERE right-leg IS NULL" idiom is equivalent to "NOT EXISTS ( ... FROM right leg)". It generates the same query plan. Some people (such as me) prefer the "old" NOT EXISTS idiom. Just a matter of taste... – wildplasser Oct 12 '11 at 22:38