I am not sure if I translated this MySQL query into Postgresql correctly

Question

A while back, I asked about changing a MySQL query into Postgresql. And, I think I got that down, but now, I have this other query which is similar, so I did the same sort of thing, but I am not sure if it's working as intended. The Original MySQL query:

create temporary table IF NOT EXISTS temptable(id int(11) primary key, shared int(1) default 0) 
ignore (SELECT 6 as id) UNION (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 (3,4,2));

My Postgresql(9.1) version:

Create TEMP TABLE IF NOT EXISTS temptable(id int primary key, shared int default 0);
Insert into temptable SELECT x.id FROM ( SELECT user2role.userid AS id 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::%' 
UNION (SELECT groupid FROM groups where groupid in (2,3,4))) x 
LEFT JOIN temptable t USING (id) WHERE t.id IS NULL

Now, I am most worried about the ignore (SELECT 6 as id) UNION bit. I don't quite understand the point of SELECT 6 as id.

Also, this is irrelevant to this query, but I have another similar query that uses replace instead of ignore. I understand that replace is a lot like insert, just that it replaces old values if they have the same key. I couldn't think of a better way, so I just went ahead and used insert, but should I implement it in a different way instead?

Thanks for everything, guys.

What part of the PostgreSQL manual suggests that your `CREATE TABLE` statement will work? — Mike Sherrill 'Cat Recall', Oct 17 '11 at 20:50
In the [synopsis](http://www.postgresql.org/docs/current/static/sql-createtable.html). The query itself runs, I was just not sure if the right query runs. — zermy, Oct 17 '11 at 21:30
Huh. I've got 9.1 here, and my local docs say 9.1.1, but that's *not* in the synopsis. One more !@#$%^ thing to fix. You can run `SELECT 6 as id` by itself to see what it does. Apparently, whoever wrote the query wanted to make sure userid 6 got included even if it were otherwise excluded by a join, a WHERE clause, or whatever. — Mike Sherrill 'Cat Recall', Oct 17 '11 at 22:51
Yup, that was exactly it, it was a silly blunder on my part not to notice. Thanks. — zermy, Oct 18 '11 at 13:22

Erwin Brandstetter · Accepted Answer · 2011-10-17T22:13:39.690

IGNORE means that rows already present in the table will not be inserted. As you have just created the table, there cannot be any rows yet. Therefore, the option is irrelevant in this case.

MySQL REPLACE should be a

DELETE .. USING insert_list .. WHERE ...;
INSERT .. ;

in one transaction. If an INSERT without DELETE succeeds, than the result is valid. There were no rows with conflicts, so the DELETE was not necessary in the first place. However, you risk that the operation is rolled back if any of the existing rows conflict with the INSERT. That does no harm other than gaps in sequences, which are never rolled back.

Thanks again. You are as helpful as always. – zermy Oct 18 '11 at 13:22 — zermy, Oct 18 '11 at 13:22

I am not sure if I translated this MySQL query into Postgresql correctly

1 Answers1