current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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));
share|improve this question
    
What does the IGNORE do in MySQL? –  a_horse_with_no_name Jul 25 '11 at 16:20
    
The IGNORE keyword tells MySQL to discard any duplicate records without generating an error –  asdbabil Jul 25 '11 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. –  a_horse_with_no_name Jul 25 '11 at 16:33

1 Answer 1

up vote 1 down vote

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);

(And I think the same should work with MySQL)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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