MySQL查询语句转postGRE语句_将MySQL查询转换为PostgreSQL

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

解决方案

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.