Converting a mysql query to postgresql

Question

This query works great in mysql but I'm trying to run it in postgresql and it's complaining about the IGNORE keyword, which is not a pg keyword. I'm not sure how to modify it to make it do the same under pg?

DROP TABLE IF EXISTS ct_tmp_u1101_t9;
CREATE TEMPORARY TABLE ct_tmp_u1101_t9(id int primary key, shared int default 0) IGNORE (SELECT 1101 as id);
SELECT shared from ct_tmp_u1101_t9
UNION (SELECT cust_user2role.userid AS userid FROM cust_user2role
INNER JOIN cust_users ON cust_users.id = cust_user2role.userid
INNER JOIN cust_role ON cust_role.roleid = cust_user2role.roleid 
WHERE cust_role.parentrole like 'H1::H2::H3::H4::H5::%')
UNION (SELECT groupid FROM cust_groups where groupid in (3,4,2,1005));

The IGNORE keyword tells MySQL to discard any duplicate records without generating an error — asdbabil
– asdbabil, Commented Jul 25, 2011 at 16:25
How could that temp table contain rows if you dropped it the line before? You drop the table, re-create it and populate it with a single row (where shared gets the value 0 and the id = 1101). What is the intention behind this? I have the feeling that the temp table is not necessary at all. — user330315
– user330315, Commented Jul 25, 2011 at 16:33

user330315 · Accepted Answer · 2011-07-25 16:36:25Z

1

Unless I'm missing something absolutely obvious, I don't see the need for the temp table.

The following should return exactly the same result (unless there is some MySQL magic involved with temp tables)

SELECT 0 as shared
UNION 
SELECT cust_user2role.userid AS userid 
FROM cust_user2role
   INNER JOIN cust_users ON cust_users.id = cust_user2role.userid
   INNER JOIN cust_role ON cust_role.roleid = cust_user2role.roleid 
   WHERE cust_role.parentrole like 'H1::H2::H3::H4::H5::%'
UNION 
SELECT groupid 
FROM cust_groups 
WHERE groupid IN (3,4,2,1005);

Collectives™ on Stack Overflow

Converting a mysql query to postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related